I am trying to return the objects relating to a through table which counts the number of reactions on a blog post.

I have an Article model, Sentiment model and Reactions model. The sentiment is simply a 1 or 2, 1 representing like and 2 for dislike. On the frontend users can react to an article and their reactions are stored in a Reactions table.

Reactions model

class Reaction(models.Model):
    user_id = models.ForeignKey(User, related_name='user_id', on_delete=models.CASCADE)
    article_id = models.ForeignKey(Article, related_name='article_id', on_delete=models.CASCADE)
    sentiment = models.ForeignKey(Sentiment, related_name='sentiment', on_delete=models.CASCADE)

I'd like to find the 2 most liked articles so I have written a view to handle the GET request

views.py

class MostPopularView(generics.RetrieveAPIView):
    queryset = Reaction.objects.annotate(num_likes = Count('sentiment_id')).order_by('num_likes')
    serializer_class = MostPopularSerializer

and a serializer to transform the data

serializers.py

class MostPopularSerializer(serializers.Serializer):
    class Meta:
        fields = (
            'id',
            'title',
        )
        model = Article

As the code stands now, I'm getting a response

<QuerySet [<Reaction: d745e09b-5685-4592-ab43-766f47c73bef San Francisco Bay 1>, <Reaction: d745e09b-5685-4592-ab43-766f47c73bef The Golden Gate Bridge 1>, <Reaction: dd512e6d-5015-4a70-ac42-3afcb1747050 San Francisco Bay 1>, <Reaction: dd512e6d-5015-4a70-ac42-3afcb1747050 The Golden Gate Bridge 2>]>

Showing San Francisco Bay has 2 likes and The Golden Gate Bridge has 1 like and 1 dislike.

I've tried multiple methods to get the correct response including filtering by sentiment=1 but can't get any further than this. What I'm looking for is a way to count the number of sentiment=1 fields which correspond to each article id and order them in descending order, so most liked at the top.

Edit

I've rethought my approach although I have not yet found a solution

1. Filter Reaction table by sentiment=1
2. Order by count of article_id
3. Serialize with MostPopularSerializer

I changed the View to be a ModelViewSet

class MostPopularView(viewsets.ModelViewSet):
    articles = Reaction.objects.filter(sentiment=1).annotate(num_likes = Count('article_id')).order_by('num_likes')[:4]
    # queryset = Article.objects.filter(id=articles['article_id'])
#Doesn't work by hypothetically what I'm thinking
    for article in articles:
        queryset = Article.objects.filter(id=article['article_id'])
    serializer_class = MostPopularSerializer

And the serializer to be a ModelSerializer

class MostPopularSerializer(serializers.ModelSerializer):
    class Meta:
        fields = (
            'id',
            'title',
            'tags',
        )
        model = Article

and an updated URL for good measure path('popular', views.MostPopularView.as_view({'get': 'list'}))

Any tips on achieving these steps would be much appreciated, thank you

CodePudding user response：

It makes no sense to use the Reaction as queryset, you use the Article as queryset, so:

from django.db.models import Case, Value, When


class MostPopularView(generics.RetrieveAPIView):
    queryset = Article.objects.annotate(
        sentiment=Sum(
            Case(
                When(article_id__sentiment_id=1, then=Value(1)),
                When(article_id__sentiment_id=2, then=Value(-1)),
            )
        )
    ).order_by('-sentiment')
    serializer_class = MostPopularSerializer

---

Note: It is normally better to make use of the settings.AUTH_USER_MODEL ^[Django-doc] to refer to the user model, than to use the User model ^[Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

---

Note: Normally one does not add a suffix …_id to a ForeignKey field, since Django will automatically add a "twin" field with an …_id suffix. Therefore it should be user, instead of user_id.

---

Note: The related_name=… parameter ^[Django-doc] is the name of the relation in reverse, so from the User model to the Reaction model in this case. Therefore it (often) makes not much sense to name it the same as the forward relation. You thus might want to consider renaming the user relation to reactions.

CodePudding user response：

I solved a simular Problem a different way. For me I wanted to sort a queryset of Person by how often the Country was used.

I added a property to the Model

class Country(models.Model):
    .
    .
    def _get_count(self):
        count = len(Person.objects.filter(country=self.id))

        return count or 0

    count = property(_get_count)

In the View I have this queryset

qs = sorted(Country.objects.all(), key=lambda country: country.count*-1)

I needed to use python sorted because the django qs.order_by can not sort by property. The *-1 is for descending order