CODEWITHSUNDEEP
pythondjangodjango-queryset
Sandeep Balagopal
Sandeep Balagopal

Reputation: 1983

Django get number of of likes group by category

My Model structure is given below

from django.db import models


class ArticleCategory(models.Model):
    name = models.CharField(('Name'), max_length=255)


class Article(models.Model):
    category = models.ForeignKey('articles.ArticleCategory', null=True,
    blank=True, related_name='articles', on_delete=models.SET_NULL)


class ArticleLike(DateBaseModel):
    user = models.ForeignKey(settings.AUTH_USER_MODEL,on_delete=models.CASCADE, related_name='user_likes')
    article = models.ForeignKey('articles.Article', on_delete=models.CASCADE, related_name='article_likes')

I have a query like below

from django.contrib.auth import get_user_model
User = get_user_model()
condition = """
         SELECT (
         SELECT COUNT(articles_articlelike.id) FROM articles_articlelike
         WHERE articles_articlelike.user_id = account_account.id
         ) AS "likes_count", "account_account"."id" FROM "account_account"
         """
users = User.objects.raw(condition)

This gives me the queryset of users with their like count. Now what I am trying to do is add the top liked category to this query. This is to show the admin which category a user liked most. How can i create a query like this ?

Upvotes: 1

Views: 229

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477160

Please do not make raw queries. It is usually unsafe, and furthermore unstable: if you later change the name of a database column, you will need to rewrite all sorts of queries.

You can implement such query with:

from django.db.models import Count, F

ArticleLike.objects.values(
    'user_id'
    category_id=F('article__category_id')
).annotate(
    nlinkes=Count('pk')
).order_by('user_id', 'category_id')

This will generate a queryset that looks like:

<QuerySet [
    { 'user_id': 14, 'category_id': 25, 'nlinkes': 16 },
    { 'user_id': 14, 'category_id': 36, 'nlinkes': 3 },
    { 'user_id': 21, 'category_id': 7, 'nlinkes': 4 }
]>

So a QuerySet of dictionaries that contain a user_id, a category_id, and the number of likes (nlikes).

Upvotes: 2

Related Questions