CODEWITHSUNDEEP
pythondjangopython-3.xdjango-modelsdjango-queryset
SusyP
SusyP

Reputation: 149

Rank and count elements of list of lists in django

I have a Genre and a User models. Each Genre has an id and a name, and there is a many-to-many relationship, so that each User likes 0 or more Genres.

Is there an efficient query for obtaining how many likes has each Genre, across all users, in descendant order?

My current approach works, but it is extremely inefficient since it has to read all the likes of all the users for each genre:

In [1]: genres = list(Genre.objects.values('name', 'id'))

In[2]: genres
Out[2]: 
[{'id': 10, 'name': 'Rock'},
 {'id': 11, 'name': 'Pop'},
 {'id': 12, 'name': 'Hip hop'},
 {'id': 13, 'name': 'Electronic'},
 {'id': 14, 'name': 'Classical'}]

In [3]: likes_by_users = []

In [4]: users = list(User.objects.all())

In [5]: for u in users:
...:     current_user_likes = []
...:     likes_by_users.append(current_user_likes)
...:     for lg in u.liked_genres.all():
...:         current_user_likes.append(lg.pk)

In [6]: likes_by_users
Out[6]: 
[[14],
 [11, 12],
 [11, 10, 13, 12],
 [],
 [13, 12, 10, 1
 [10, 11]]

In [7]: counts = {}

In [8]: for g in genres:
    ...:     counts[g['id']] = {
    ...:         'name' : g['name'],
    ...:         'likes': 0
    ...:     }
    ...:     for l in likes_by_users:
    ...:         for gid in l:
    ...:             if gid == g['id']:
    ...:                 counts[gid]['likes'] += 1


In [9]: ranking = sorted(list(counts.values()), key=lambda x : x['likes'], reverse=True)

And this is exactly the output that I need:

In [9]: ranking
Out[9]: 
[{'likes': 4, 'name': 'Pop'},
 {'likes': 3, 'name': 'Rock'},
 {'likes': 3, 'name': 'Hip hop'},
 {'likes': 2, 'name': 'Electronic'},
 {'likes': 1, 'name': 'Classical'}]

Is there a query or another method for obtaining in an efficient way the required ranking?

Upvotes: 1

Views: 579

Answers (2)

AKX
AKX

Reputation: 168913

ManyToMany fields in Django are backed by actual models (and thus tables).

You can get at the backing model via .through:

GenreLikes = User.liked_genres.through

For a simple many-to-many relation, the model will have two fields/columns, user and genre in in your case, one instance/row per user-like pair.

Thus, you should be able to get your ranking with just

GenreLikes.objects.values('genre').annotate(likes=Count('user')).order_by('-likes')

or similar.

Upvotes: 1

weAreStarsDust
weAreStarsDust

Reputation: 2752

Try this

from django.db.models import Count
Genre.objects.annotate(likes=Count('user')).order_by('-likes')

Upvotes: 1

Related Questions