制定具有高度挑战性要求的高效数据库查询（Django应用程序）

This is a tough one. I have a django-based web-app where users can post interesting URLs, and have spectators comment under each posting. Moreover, I use stealth banning against misbehaving users.

这是困难的一个。我有一个基于django的网络应用程序，用户可以发布有趣的URL，并在每个帖子下有观众评论。此外，我使用隐形禁止来防止行为不端的用户。

Interesting URLs are represented by Link model; comments under the posting are reprenseted by Publicreply model.

有趣的URL由Link模型表示; Publicreply模型重新发布了帖子下的评论。

Here's how these models are related to one other:

以下是这些模型之间的相互关系：

class Link(models.Model): 
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)
    url = models.URLField(max_length=250)

class Publicreply(models.Model):
    submitted_by = models.ForeignKey(User)
    answer_to = models.ForeignKey(Link)
    submitted_on = models.DateTimeField(auto_now_add=True)
    description = models.TextField(validators=[MaxLengthValidator(250)])

Users I ban go here:

我禁止的用户去这里：

class HellBanList(models.Model):
    condemned = models.ForeignKey(User)
    when = models.DateTimeField(auto_now_add=True)

What's my question? I'm trying to write the best performing, optimized query that gets me:

我的问题是什么？我正在尝试编写性能最佳，优化的查询来获取：

1. The publicreply with the absolutely latest timestamp FROM among all distinct links THAT either the user herself posted, OR below which the said user left a publicreply

1.公众反映在用户自己发布的所有不同链接中的绝对最新时间戳FROM，或者低于该用户自己发布的公共反馈

2. But if the publicreply object gotten in 1 was of someone who's part of HellBanList (i.e. condemned attribute), I'd want to ignore that particular publicreply and dig up the next most-recent one. If that's of a banned user too, I'll skip to the next (and so on).

2.但是，如果1中的公共反对对象属于HellBanList的一部分（即被谴责的属性），我想忽略那个特定的公共反应并挖掘下一个最近的那个。如果那也是被禁用的用户，我将跳到下一个（依此类推）。

3. Finally, I only need submitted_by and submitted_on attribute values from the publicreply object I end up with after catering for both 1 and 2.

3.最后，我只需要在服务于1和2后最终得到的publicreply对象中的submitted_by和submitted_on属性值。

So what have I tried thus far? This:

那么到目前为止我尝试了什么？这个：

freshest_link = Link.objects.filter(Q(submitter=self.request.user)|Q(publicreply__submitted_by=self.request.user)).distinct().annotate(date=Max('publicreply__submitted_on')).latest('date')
freshest_reply = Publicreply.objects.filter(answer_to=freshest_link).latest('submitted_by')

What's the problem with this code? The fact that it doesn't cater to requirement 2. I think I ought to have somehow run an exclude() on publicreplys posted by users in HellBanList before calculating freshest_reply (in order to meet requirement 2). Anyhow I can't figure out how to do that. Secondly, an expert can perhaps build the whole query more efficiently than how I've attempted it, goal being minimum possible DB roundtrips.

这段代码有什么问题？事实上它不符合要求2.我认为我应该在计算freshest_reply之前以某种方式在HellBanList中的用户发布的publicreplys上运行exclude（）（以满足要求2）。无论如何我无法弄清楚如何做到这一点。其次，专家可以比我的尝试更有效地构建整个查询，目标是尽可能少的DB往返。

Can you help out in this? If you feel I've been opaque in my description of what I'm trying to achieve, do ask for clarification.

你可以帮帮忙吗？如果你觉得我在描述我想要达到的目标时一直不透明，那么请要求澄清。

Note: my DB is postgres

注意：我的数据库是postgres

1 个解决方案

#1

Get the banned users list like:

获取禁止的用户列表，如：

banlist = HellBanList.objects.values_list('condemned',flat=True)

modify freshest_reply query like:

修改freshest_reply查询，如：

freshest_reply = Publicreply.objects.filter(answer_to=freshest_link).exclude(submitted_by_id__in=banlist).latest('submitted_on')

2nd method:

第二种方法：

In your query, first you get the Link which has the latest public reply and in the next query you get the latest Publicreply for that Link. Instead of that we can just get the latest reply in one query like:

在您的查询中，首先获得具有最新公开回复的链接，并在下一个查询中获得该链接的最新Publicreply。而不是我们可以在一个查询中获取最新的回复，如：

latest_reply = Publicreply.objects.filter(Q(answer_to__submitter=self.request.user)|Q(submitted_by=self.request.user)).exclude(submitted_by_id__in=banlist).latest('submitted_on')

Let me know if I missed something.

如果我错过了什么，请告诉我。

#1