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