使用django ORM，仅使用最新小孩获取父母的优化方式 [英] Optimized way of fetching parents with only latest child using django ORM

问题描述

我们想以这样的方式取得父母，给我最新的10个父母，每个父母只有一个最新的孩子记录。

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.

例如：

Category
- id
- name
- created_date

Item
- id
- name
- category
- created_date

使用上述指定的模型结构，我想获取最新的10个类别以及最新的子项每个类别。

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

使用django进行优化的方法是什么？ ORM。

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

这可以使用以下sql查询。我宁愿使用django ORM来解决这个问题。或者更好的sql查询。

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

推荐答案

使用原始SQL，您的查询看起来很好（在最终限制之前，缺少c.created_date desc命令。

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.

原始SQL中的子查询解决方案并不比使用返回类别模型中最后10个条目的方法好得多（例如，未经测试）：

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]

我们正在谈论最多100条记录，我不用担心效率，因为后续查询就像打高速缓存一个几层缓存：django，数据库和操作系统）。这样，您可以传递最后10个类别，并从模板中调用last10item：

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 %}
      ...

我敢肯定，一些混蛋会downvote我说：不要尝试优化的东西，除非你需要。在许多情况下，我很惊讶，在对代码进行剖析之前，我对一种方法的效率有多么错误。阅读 Python模式 - 优化轶事。

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".

这篇关于使用django ORM，仅使用最新小孩获取父母的优化方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！