在Django中重写此数据库访问查询以规避“ DISTINCT ON”错误 [英] Rewriting this database access query to circumvent 'DISTINCT ON' error, in Django

问题描述

我有两个非常简单的模型：

I have two very simple models:

class Link(models.Model):
    description = models.TextField(validators=[MaxLengthValidator(500)])
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)

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)])

为了获取最新的<$ c $对于每个 link 对象，我尝试了c> publicreply ： latest_replies = Publicreply.objects.filter（answer_to_id__in = link_ids） .order_by（'answer_to'，'submitted_on'）。distinct（'answer_to'）其中， link_ids 是所有 link 在这种特殊情况下我需要的对象。

In the bid to get the latest publicreply for each link object, I tried this: latest_replies = Publicreply.objects.filter(answer_to_id__in=link_ids).order_by('answer_to', 'submitted_on').distinct('answer_to') where link_ids is a list of all link objects I needed in this particular case.

问题是，我得到了一个区别该数据库后端不支持lds 。我在本地使用SQLite，在生产中使用Postgres。有什么方法可以重写此查询，使我得到相同的结果？请注意，我不想在FOR循环下运行数据库查询-我已经对此进行了分析，结果非常糟糕。

Problem is, I get a DISTINCT ON fields is not supported by this database backend. I'm on SQLite locally, Postgres in production. What's a way to re-write this query such that I get the same result? Note that I don't want to run a database query under a FOR loop - I've profiled that and the results are very unsavory. Is there a single query to get the job done?

推荐答案

您只需照顾 distinct 在内存中。

replies_by_link = {}
replies = Publicreply.objects.filter(answer_to_id__in=link_ids).order_by('answer_to', 'submitted_on')

for reply in replies:
    if reply.answer_to_id not in replies_by_link:
        replies_by_link[reply.answer_to_id] = reply

，然后您可以通过 replies_by_link.values访问所有答复）。

这篇关于在Django中重写此数据库访问查询以规避“ DISTINCT ON”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！