Django; How to make multiple annotations in one set of queries

Question

I'm currently trying to annotate two different types of likes for the User model in Django.

Here is the code that I use 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 two annotated values ​​and multiplies them together, and so I get 612072.

Is there a way to call multiple annotations in the same set of queries without getting these multiplier values.

EDIT: Also tried adding distinct () at the end of the query or highlight = True in each count, but the call just ends up in an infinite loop.

Answer 1

This is how django annotate creates sql code: it performs all the necessary joins and then is grouped by all fields of the User, aggregating with the annotation function (account in your case). Thus, he joins users with all their favorite guides, and then with all the news, and then simply counts the number of lines created for each user.

If you can, you should use the raw querysets or extra query method. For instance:

 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 select_params flexibility, you can use the select_params parameter of the extra method to provide table names (which you can go through Model._meta ). By the way, this is a very inconvenient and hacker method. Sooner or later, your logic gets complicated, and then you have to remove it from python code in sql (stored functions / procedures) and raw queries.