Optimized way of fetching parents with only latest child using django ORM

Question

We want to fetch parent child in such a way that it gives me latest 10 parents with each having only one latest child record.

For example:

Category
- id
- name
- created_date

Item
- id
- name
- category
- created_date

Using above specified model structure, I would like to fetch latest 10 categories along with latest child item for each category.

With only one query to the server I would like to access all the data.

Category1.name, Category1.id, LatestItemForCat1.name, LatestItem1ForCat1.created_date
Category2.name, Category2.id, LatestItemForCat2.name, LatestItem1ForCat2.created_date
Category3.name, Category3.id, LatestItemForCat3.name, LatestItem1ForCat3.created_date

What is the optimized way to achive this using django ORM.

This can be achived with the following sql query. I would prefer to use django ORM for solving this problem. Or better sql query.

select c.name, c.id, i.name, i.created_date
from
    category c
inner join
    item i
on c.id = i.category_id
where i.id = (select id from item order by created_date desc limit 0,1)
limit 10

Answer 1

You can resort to raw SQL, and your query looks fine (just lacks an "order by c.created_date desc" before the limit in the end.

The subquery in the raw SQL solution is not much better than having a method returning the 10 last entries in the Category model, something like this (untested) sample:

class Category(models.Model):
    ...
    def last10items(self):
        return self.item_set.order_by('-created_date')[:10]

We are talking about 100 records maximum, I would not worry about efficiency because subsequent queries are like to hit the cache (there are several layers of cache: django, database and OS). This way you can pass the last 10 categories and call last10items from the template:

{% for category in categories %}
  ...
  {% for item in category.last10items %}
      ...

I'm sure some jerk will downvote me for saying this: do not try to optimize something unless you need to. In many situations I was surprised how wrong I was about efficiency of one approach over another until profiling the code. Read "Python Patterns - An Optimization Anecdote".