How to get few last rows from related table?

Question

Need help with a orm request.

There are 2 tables. Author and Book (names for example, so you don't need to look at the logic), linked through FK.

class Book(models.Models):
  title = models.Charfield(...)
  
  
class Author(models.Model):
  book = models.ForeignKey(Book)

Need to group the authors by the book and go through them in a loop. The question is how to select only the last 50 authors of each book.

I can write this:

for book in Book.all()
    for author in  book.author_set.all()[:50]:
        ....

But this is not an optimal solution.

Why is that not optimal? What is the solution you're looking for? — Vincent, May 26 '21 at 17:46
@WillemVanOnsem Yes. Name of `tables` I wrote for example. If you want, can change `Author` to `BookInstance`... — Dima, May 26 '21 at 17:51
@Vincent I don't want to make query to database in each *book* iteration... — Dima, May 26 '21 at 17:57
@WillemVanOnsem I need this on `django orm` - https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group — Dima, May 26 '21 at 18:12
@WillemVanOnsem http://www.silota.com/docs/recipes/sql-top-n-group.html — Dima, May 26 '21 at 18:15

lucutzu33 · Answer 1 · 2021-05-26T18:30:45.303

Bad answer, I didn't realize that slicing implies a new query. My bad, don't look further down.

To optimize your query, you need to use the prefetch_related() method:

prefetch_related() Returns a QuerySet that will automatically retrieve, in a single batch, related objects for each of the specified lookups.

This has a similar purpose to select_related, in that both are designed to stop the deluge of database queries that is caused by accessing related objects, but the strategy is quite different.

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query. However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one.

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related.

for book in Book.all().prefetch_related()
    for author in  book.author_set.all()[:50]

You also need to order your book.author_set queryset to make sure you get the latest entries.

Wouldn't there be a query on every iteration of the book in this case? — Dima, May 26 '21 at 17:58
`prefetch_related` is not going to help any here, that slice `[:50]` implies a _new_ query, hence in fact all that prefetching is going to be wasteful. — Abdul Aziz Barkat, May 26 '21 at 18:00

How to get few last rows from related table?

1 Answers1