Django remove duplicates in queryset and access their list of foreign key

Question

Here is my model:

class Item(models.Model):
    title = models.TextField()    

class Storage(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    rating = models.IntegerField(blank=True, null=True)
    review = models.CharField(max_length=1500, blank=True, null=True)

class SocialProfile(models.Model):
    user = models.OneToOneField(User, unique=True)
    follows = models.ManyToManyField('self', related_name='followed_by', symmetrical=False)

Say there are 4 users A,B,C,D. User A follows only B and C. I'm trying to do a view that when log in as User A. Show a list of items that B and C has (duplicates show only one time). And under each item, show their summary and ratings.

I have something here: (user = User A here)

//get a list of users that User A follows
followings = user.socialprofile.follows.all()
//get B and C's saved item, eliminate the duplicates 
context['following_saved_article'] = Item.objects.filter(storage__user__in=followings.values('user')).distinct()

//get a flat list of all users A follows
context['followings'] = list(followings.values_list('user', flat=True).order_by('user'))

Then in the template:

{% for item in following_saved_item %}
    {{ item.title }}
    {% for x in item.storage_set.all %}
         {% if x.user_id in followings %}
              {{ x.review }} {{ x.rating }}
         {% endif %}
    {% endfor %}
{% endfor %}

This seems too redundant, and I will have a pretty hard time if I want to sort reviews by user rating.

Is there any way to generate a list of distinct Item instance, fetch only the storage_set based on following, sort by rating or other field in storage, and pass that to view? Thanks

Answer 1

I think a custom Prefetch object will do the trick

followings = user.socialprofile.follows.all()

items = Item.objects.filter(
            storage__user__in=followings.values('user')
        ).distinct().prefetch_related(
            models.Prefetch('storage_set',
                queryset=Storage.objects.filter(
                   user__in=followings.values('user')
                ),
                to_attr='storage_from_followings'
            )
        )

#in the template
{% for item in items %}
    {{ item.title }}
    {% for x in item.storage_from_followings %}
        {{ x.review }} {{ x.rating }}
    {% endfor %}
{% endfor %}