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?
Models are like this:
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)
Try this:
distinct_users_problems = Submission.objects.all().values("user", "problem").distinct()
It will give you a list of dicts like this one:
[{'problem': 1, 'user': 1}, {'problem': 2, 'user': 1}, {'problem': 3, 'user': 1}]
containing all the distinct pairs.
It actually results in your usual SELECT DISTINCT SQL query.
Update 2:
(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)
You can then either
Submission.save()
to create/update the entry in LatestSubmission
every time an user posts a new solution for a Problemsuch 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')
Update:
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.
Original Answer
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With