Count and group by foreign key and return model django

Question

Data Model:

Each Library has many Books
Each Book may or may not be read by a User dictated by the UserBookReceipt

Objective:

Given a user, order the libraries by how many books they have read at each library.

Example:

User 1 has read:

2 Books from Library 1
5 Books from Library 2
1 Book from Library 3
0 Books from Library 4

We should return the libraries in order of number of books read by User 1 ie:

Library 2, Library 1, Library 3

Current solution

libraries = (UserBookReceipt.objects
    .filter(user=user)
    .values('book__library')
    .annotate(rcount=Count('book__library'))
    .order_by('-rcount')
)

Actual output

[
    {'book_library': 2, 'rcount': 5}, 
    {'book_library': 1, 'rcount': 2}, 
    {'book_library': 3, 'rcount': 1}
]

Expected output

[
    {'book_library': <Library: 2>, 'rcount': 5}, 
    {'book_library': <Library: 1>, 'rcount': 2}, 
    {'book_library': <Library: 3>, 'rcount': 1}
]

The actual output is 90% of what I want, except I want the book_library value to be instances of the django Library model rather than just the library id. Otherwise, I have to make another query to retrieve the Library objects which would probably require some inefficient ids__in query.

How can I count and group by the UserBookReceipt.book__library and return the Library model?

If I were to do this in SQL the query would look like

with rcount_per_lib as (
    select lib.id, count(*) as rcount
    from lib, books, user_book_receipts
    where user_book_receipts.book_id = books.id
        and lib.id = books.lib_id 
        and user_book_receipts.user_id = 1
    group by lib.id)
select lib.*, rcount from rcount_per_lib 
where lib.id = rcount_per_lib.id 
order by rcount

If you are going to use SQL, you should really to use proper, explicit, **standard** `JOIN` syntax. — Gordon Linoff, Nov 17 '18 at 14:30
Why? AFAIK the `and` syntax is equivalent performance wise to `INNER JOIN` — david_adler, Nov 17 '18 at 14:36

score 2 · Accepted Answer · edited Nov 18 '18 at 13:25

2

You need to change how you approach the queryset. Use Library rather than UserBookReceipt.

libraries = (Library.objects
    .filter(book__userbookreceipt__user=user)
    .annotate(rcount=Count('book__userbookreceipt', distinct=True))
    .order_by('-rcount')
)
[x.rcount for x in libraries]

edited Nov 18 '18 at 13:25

david_adler

9,690
6
57
97

answered Nov 17 '18 at 15:51

schillingt

13,493
2
32
34