Django等价于每个用户的最新条目

I'm surprised this question hasn't come up. Couldn't find much on the web.

我很惊讶这个问题没有被提出来。在网上找不到什么。

Using Entry.objects.latest('created_at') I can recover the latest entry for all Entry objects, but say if I want the latest entry for each user? This is something similar to an SQL latest record query. But how do I achieve this using the ORM? Here is my approach I'm wondering if it is the most efficient way to do what I want.

使用Entry.objects.latest('created_at')，我可以恢复所有条目对象的最新条目，但是如果我想为每个用户恢复最新的条目，您会怎么说?这类似于SQL最新记录查询。但是如何使用ORM实现这个目标呢?这是我的方法，我想知道这是否是做我想做的事情的最有效的方法。

First I perform a sub query: Objects are grouped by user and the Max (latest) created_by field is returned for each user (created_at__max) I then filter Entry objects based on the results in the subquery and get the required objects.

首先，我执行一个子查询:对象被用户分组，并且每个用户(created_at__max)为每个用户返回created_by字段(created_at__max)，然后根据子查询中的结果筛选条目对象并获取所需的对象。

Entry.objects.filter(created_at__in=Entry.objects.values('user').annotate(Max('created_at')).values_list('created_at__max'))

or using a manager:

或者使用一个经理:

class UsersLatest(models.Manager):  

    def get_query_set(self):
        return super(UsersLatest,self).get_query_set().filter(created_at__in=self.model.objects.values('user').annotate(Max('created_at')).values_list('created_at__max'))

Is there a more efficient way? possibly without sub query?

有没有更有效的方法?可能没有子查询吗?

Thanks,

谢谢,

Paul

保罗

5 个解决方案

#1

Using order_by and distinct:

使用order_by和不同的:

Entry.objects.all().order_by('user', 'created_at').distinct('user')

Then for performance adding index together on 'user' and 'created_at' fields.

然后在“user”和“created_at”字段上添加性能索引。

But i think real production way is to use Redis to cache and update an id's list of latest entries of users.

但是我认为真正的生产方式是使用Redis来缓存和更新id的最新用户条目列表。

#2

The design of your QuerySet depends on what you plan to use it for. I'm not sure why you're breaking out of the QuerySet iterator with the values_list method at the end. I imagine you have a status list of users where you show the last activity time based on that Entries model. For that you may want to try this:

查询集的设计取决于您计划使用它做什么。我不知道为什么要在最后用values_list方法跳出QuerySet迭代器。我假设您有一个用户状态列表，其中显示基于该条目模型的最后活动时间。你可能想试试这个:

Users.objects.all().annotate(latest_activity=Max('entries__created_at'))

And then loop through your users easily in your template with

然后在模板中很容易地循环用户

{% for user in users %}
{{ user.full_name }}
{{ user.latest_activity|date: "m/d/Y" }}
{% endfor %}

#3

The raw SQL would be

原始SQL将是

SELECT entry.id, entry.title, entry.content, entry.user_id, entry.created_at
FROM
    entry
WHERE
    entry.created_at = ( SELECT Max(e2.created_at) from entry as e2 where e2.user_id = entry.user_id )

So one option is using the where argument of the extra() modifier:

因此，一个选项是使用额外()修饰符的where参数:

Entry.objects.extra(where='entry.created_at = ( SELECT Max(e2.created_at) from entry as e2 where e2.user_id = entry.user_id )')

Of course, you'd probably have to change entry to whatever the actual name of the table is in the database. Assuming you're comfortable looking at ._meta, you can try this:

当然，您可能需要更改条目到数据库的实际名称。假设你很乐意看到。_meta，你可以试试这个:

Entry.objects.extra( where=
    '%(table)s.created_at = ( SELECT Max(e2.created_at) from %(table)s as e2 where e2.user_id = %(table)s.user_id )' % { 'table':Entry._meta.db_table }
)

There's probably a more elegant way to get the name of a table.

可能有一种更优雅的方法来获取表的名称。

#4

I cannot think out a single raw sql query which will fetch the set you need, so I doubt it's possible to construct a QuerySet with these results.

我无法想出一个原始sql查询来获取您需要的集合，因此我怀疑是否有可能用这些结果构造一个QuerySet。

#5

I had a similar problem and did it this way:

我也遇到过类似的问题，我是这样做的:

priorities = obj.books_set.values('category').annotate(priority=Max('priority'))

Note: I annotate max priority as priority, because I'll reuse the output as filter condition.

注意:我将最大优先级标注为priority，因为我将把输出作为筛选条件重用。

It's a list of categories with min priorities. Then I do this:

它是一个有最小优先级的类别列表。然后我这样做:

>>> priorities[0]
{'category': 1, 'priority': 10}

I want to find books that have category & priority pair among one in the list. In the end the queryset condition should look like this:

我想在列表中找到有类别和优先级对的书。最后，queryset条件应该如下所示:

Q(priorities[0]) | Q(priorities[1]) | ...

To do this in one line, use reduce on Q.__or__:

要做到这一点，请在Q.__or__上使用reduce:

reduce(Q.__or__, (Q(**x) for x in priorities))

I know it's a bit worse than raw SQL, but safer. Comment this code if you use it, because it's hard to read.

我知道这比原始SQL更糟糕，但更安全。如果使用此代码，请注释它，因为它很难阅读。

#1