Django查询按字段对选择不同 [英] Django query select distinct by field pairs

问题描述

我有字段'提交'有一个用户和一个问题。如何获得一个SQL搜索结果，这将给出每个用户问题对只有一个结果的列表？

I have the field 'submission' which has a user and a problem. How can I get an SQL search result which will give a list of only one result per user-problem pair?

模型是这样的：

class Problem(models.Model):
    title = models.CharField('Title', max_length = 100)
    question = models.TextField('Question')

class Submission(models.Model):
    user = models.ForeignKey(User)
    problem = models.ForeignKey(Problem)
    solution = models.CharKey()
    time = models.DateTimeField('Time', auto_now_add=True)

推荐答案

更新2 ：

（阅读OP的评论后）新模式跟踪最新提交。称为 LatestSubmission 。

(After reading OP's comments) I suggest adding a new model to track the latest submission. Call it LatestSubmission.

class LatestSubmission(models.Model):
    user = models.ForeignKey(User)     
    problem = models.ForeignKey(Problem)
    submission = models.ForeignKey(Submission)

1. 覆盖 Submission.save（）创建/更新 LatestSubmission 每次用户发布一个问题的新解决方案


2. 附加一个相同的功能到一个合适的信号。

1. override Submission.save() to create/update the entry in LatestSubmission every time an user posts a new solution for a Problem
2. attach a function that does the same to a suitable signal.

，以便 LatestSubmission 将包含一行每个问题 - 用户提交组合指出每个用户最新提交的问题。一旦你有了这个，你可以点击一个查询：

such that LatestSubmission will contain one row per problem-user-submission combination pointing to the latest submission for the problem by each user. Once you have this in place you can fire a single query:

LatestSubmission.objects.all().order_by('problem')

更新：

由于OP已经发布了示例代码，现在可以更改以下解决方案：

Since the OP has posted sample code, the solution can now be changed as follows:

for user in User.objects.all(): # Get all users
    user.submission_set.latest('time') # Pick the latest submission based on time.

原始答案

如果没有任何基于日期/时间的条件来决定哪个是较旧的或较新的，那么您可以使用$ $的主键（ id ） c $ c>提交以忽略旧的。

In the absence of any date/time based criteria for deciding which is "older" or "newer", you can use the primary key (id) of Submission to "neglect the old ones".

for user in User.objects.all(): # Get all users
    user.submission_set.latest('id') # Pick the latest submission by each user.

这篇关于Django查询按字段对选择不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！