9

I am currently trying to annotate two different number of likes to a User model in Django.

Here's the code I'm using to return the desired querySet

def get_top_user(self):
    return User.objects. \
        annotate(guide_like=Count('guidelike')).\
        annotate(news_like=Count('newslike')).\
        values_list('first_name', 'last_name', 'guide_like','news_like').\
        order_by('-guide_like')

However, the querySet returns ["Bob", "Miller", 612072, 612072]. As you can see, Django takes the two annotate values and multiply them together and that's why I'm getting 612072.

Is there a way to call multiple annotate in a single querySet without getting these multiplied values.

EDIT: Also tried to add distinct() at the end of the query or distinct=True in each count but the call simply gets into an infinite loop.

Jean-Michel Provencher
  • 103
  • 1
  • 1
  • 7
  • 1
    possible duplicate of [Django annotate() multiple times causes wrong answers](http://stackoverflow.com/questions/1265190/django-annotate-multiple-times-causes-wrong-answers) – zxzak Jun 17 '15 at 19:48
  • 2
    The only solution was to add distinct=True which is not a good pratice and doesn't work in my case. – Jean-Michel Provencher Jun 17 '15 at 20:08
  • Can you show GuideLike and NewsLike models? – ZAN Jun 25 '15 at 16:08
  • Also, please show results for separate guide_like and news_like annotations calls – ZAN Jun 25 '15 at 16:09
  • @Jean-MichelProvencher I was actually facing that problem, and `distinct` solves it. Why you say it is not a good practice? – Lemayzeur Nov 15 '21 at 18:47

1 Answers1

13

This is how django annotate produce sql code: it's do all necessary joins and then group by over all User fields, aggregating with annotation function(count in your case). So, it joins users with all their guide likes and then with all news likes and then simply counts number of rows produced per user.

If you can, you should use raw querysets, or extra Queryset method. E.g:

User.objects.all().extra(select={
    'guide_likes': 'select count(*) from tbl_guide_likes where user_id=tbl_users.id',
    'news_like': 'select count(*) from tbl_news_likes where user_id=tbl_users.id'
}).\
values_list('first_name', 'last_name', 'guide_like','news_like')

For more flexibility you can use select_params parameter of extra method for providing names of tables(which you can get through Model._meta). By the way this is very unconvenient and hackish method. Sooner or later your logic become more complicated and then you should remove it from python code to sql(stored functions/procedures) and raw queries.

kammala
  • 427
  • 3
  • 9