Django：每个外键返回一个过滤的对象 [英] Django: return one filtered object per foreign key

问题描述

是否可以返回每个外键只返回一个对象的查询器？

Is it possible to return querysets that return only one object per foreign key?

例如，我想要从django_comments获取最新的评论，但我只想要一个对象的评论（最新评论），即只返回最新评论在对象上排除所有过去对该对象的注释。我想这将类似于django_comments.content_type和django_comments.object_pk上的sql group_by。

For instance, I want the to get the latest comments from django_comments, but I only want one comment (the latest comment) per object, i.e., only return the latest comment on an object and exclude all the past comments on that object. I guess this would be similar to a sql group_by on django_comments.content_type and django_comments.object_pk.

++添加INFO ++

++ADDED INFO++

最终目标是创建一个活动注释线程列表，该列表显示/排序由哪个线程拥有最新的评论，就像您的标准讨论板，其主题由最近的活动列出。

The end goal is to create a list of active comment "threads" displayed/ordered by which thread has the most recent comment, just like your standard discussion board whose topics are listed by recent activity.

我认为最好的方法是抓住最新的评论，然后按内容类型和object_pk排序或分组，以便只有一个评论（最新）相关内容对象返回。然后我可以使用该注释来获取所需的所有信息，因此我的线程被松散地使用，因为我真的只是抓住一个评论，并遵循它的pk的。

I figure the best way to do this would be grabbing the latest comments, and then sorting or grouping them by content type and object_pk so that only one comment (the latest) is returned per related content object. I can then use that comment to get all the info I need, so the word thread is used loosely since I'm really just grabbing a comment and following it's pk's.

MODEL是django_threadedcomments，它将django_comments扩展到树，子和父母的一些添加字段。

The MODEL is django_threadedcomments which extends django_comments with some added fields for trees, children, and parents.

查看：

...这将返回所有评论，包括所有父级实例

...this returns all comments including all instances of parent

comments = ThreadedComment.objects.all().exclude(is_public='0').order_by("-submit_date")

...这是理想的

comments = ThreadedComment.objects.all().exclude(is_public='0').order_by("submit_date").[plus sorting logic to exclude multiple instances of the same object_pk and content_type]

模板：

{% for comment in comments %}

TITLE: {{comment.content_object.title}}

STARTED BY : {{comment.content_object.user}}

MOST RECENT REPLY : {{comment.user}} on {{comment.submit_date}}

{% endfor %}

再次感谢

推荐答案

这是一件相当困难的事情完全在SQL中做;您可能无法通过ORM来执行此操作。

This is a fairly difficult thing to do in SQL at all; you probably won't be able to do it through the ORM.

您不能使用GROUP BY。这是用来告诉SQL如何分组项目进行聚合，这不是你在这里做的。 SELECT x，y FROM table GROUP BY x是非法的SQL，因为y的值是无意义的。

You can't use GROUP BY for this. That's used for telling SQL how to group items for aggregation, which isn't what you're doing here. "SELECT x, y FROM table GROUP BY x" is illegal SQL, because the value of y is meaningless.

我们来看一下清楚的架构： / p>

Let's look at this with a clear schema in mind:

CREATE TABLE objects ( id INTEGER PRIMARY KEY, name VARCHAR );
CREATE TABLE comments ( object_id INTEGER REFERENCES objects (id), text VARCHAR NOT NULL, date TIMESTAMP NOT NULL );

INSERT INTO objects (id, name) VALUES (1, 'object 1'), (2, 'object 2');
INSERT INTO comments (object_id, text, date) VALUES
   (1, 'object 1 comment 1', '2010-01-02'),
   (1, 'object 1 comment 2', '2010-01-05'),
   (2, 'object 2 comment 1', '2010-01-08'),
   (2, 'object 2 comment 2', '2010-01-09');

SELECT * FROM objects o JOIN comments c ON (o.id = c.object_id);

我看过的最优雅的方式是Postgresql 8.4的窗口功能。 p>

The most elegant way I've seen for doing this is Postgresql 8.4's windowing functions.

SELECT * FROM (
    SELECT
        o.*, c.*,
        rank() OVER (PARTITION BY object_id ORDER BY date DESC) AS r
    FROM objects o JOIN comments c ON (o.id = c.object_id)
) AS s
WHERE r = 1;

这将按日期，最新的方式为每个对象选择第一个注释。如果你没有看到这是做什么，请自行执行内部SELECT，并观察它如何生成rank（），这使得它非常简单。

That'll select the first comment for each object by date, newest first. If you don't see what this is doing, execute the inner SELECT on its own and watch how it generates rank(), which makes it pretty straightforward.

我知道使用Postgresql的其他方法，但是我不知道如何在其他数据库中执行此操作。

I know other ways of doing this with Postgresql, but I don't know how to do this in other databases.

尝试动态计算可能会给您严重的头痛 - - 并且需要更多的工作来使这些复杂的查询也表现良好。有可能你最好做这个简单的方法：存储每个对象的 last_comment_id 字段，并在添加或删除注释时对其进行更新，因此您可以加入和分类。您可能会使用SQL触发器来自动处理此更新。

Trying to compute this dynamically is likely to give you serious headaches--and it takes more work to make these complex queries perform well, too. Chances are you're better off doing this the simple way: store a last_comment_id field for each object and update it when a comment is added or deleted, so you can just join and sort. You could probably use SQL triggers to handle this updating automatically.

这篇关于Django：每个外键返回一个过滤的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！