Django query which calculate most active post by like and dislike, and by each category

Using Django
1

I want to calculate most popular post by each category, but i have this error DISTINCT ON fields is not supported by this database backend .

after i use PostgreSql , but I also had a error. annotation and distinct together did not work

model → 

    title = models.CharField(max_length=150, verbose_name=_("კატეგორია"))

    def __str__(self):
        return self.title


class Question(models.Model):
    user = models.ForeignKey(
        User, on_delete=models.CASCADE, verbose_name=_("მომხმარებელი")
    )
    category = models.ManyToManyField(Category)
    title = models.CharField(max_length=150, verbose_name=_("სათაური"))
    body = models.TextField(verbose_name=_("ტექსტი"))
    image = models.ImageField(blank=True, null=True, verbose_name=_("ფოტო"))
    link = models.URLField(
        max_length=400,
        blank=True,
        null=True,
        validators=[RequireHttpOrHttpsUrl()],
        verbose_name=_("ლინკი"),
    )
    time = models.DateTimeField(auto_now=True)
    send_notification = models.BooleanField(
        default=True, verbose_name=_("გავაგზავნოთ შეტყობინება?")
    )

    def __str__(self):
        return self.title

class LikeDislike(models.Model):
    user = models.ForeignKey(
        User, on_delete=models.CASCADE, verbose_name=_("მომხმარებელი")
    )
    question = models.ForeignKey(
        Question, on_delete=models.CASCADE, verbose_name=_("კითხვა")
    )
    point = models.BooleanField()
    time = models.DateTimeField()

    def __str__(self):
        return self.question.title

view → 

class CountLikeByCategory(generics.ListCreateAPIView):
    serializer_class = CountLikeByCategorySerializer

    def get_queryset(self):
        query=Question.objects.values_list(
            'category__title','title'
        ).annotate(
            l=Count('likedislike',filter=Q(likedislike__point=1)),
            d=Count('likedislike',filter=Q(likedislike__point=0)),
            total=F('l')+F('d'),
        ).order_by('category', '-total').distinct('category')
        return query
2

Basically, you don’t mix annotations with groupings (as you’ve seen). That’s what aggregation is for.

But I’m really confused with what you’re trying to do given that you’ve got a ManyToMany between Question and Category.

Can you put together a small data sample, perhaps 2 categories, 3 questions (one of the three related to both categories) and 6 LikeDislike (2 for each question, one with 2 likes, one with 1 like and 1 not, and one with 0 likes)? And then show what you’re looking to get as a result?

3

Hi :slight_smile:

The problem is that you cannot combine annotate with distinct because it’ll raise this error

NotImplementedError: annotate() + distinct(fields) is not implemented.

But we could achieve the same effect with this trick:

def get_queryset(self):
    questions_from_unique_categories = Question.objects.distinct('category')

    questions = questions_from_unique_categories \
        .filter(id__in=questions_from_unique_categories) \  # That's the equivalent  of `distinct('category')`
        .annotate(
            l=Count('likedislike',filter=Q(likedislike__point=1)),
            d=Count('likedislike',filter=Q(likedislike__point=0)),
            total=F('l')+F('d'),
        )
        .order_by('category', '-total')

    return questions

NOTE: when you work with filter(id__in=another_queryset) this is still making 1 SQL query that looks like this:

SELECT ...
    FROM questions
    WHERE id IN (SELECT DISTINCT category FROM questions)

Please let me know if you try this and if it worked :slight_smile:

Cheers

1 Like
4

The m2m connection is correct as the post may have several categories

5

I can understand that. However, if I’m understanding your original post correctly, what you’re describing, counting the number of each LikeDislike.point values by category, means you’re going to be counting them multiple times. Is that the result you’re looking for?