Skip to content

Instantly share code, notes, and snippets.

@MewX

MewX/gist:e3e0d5808ee24064d63f8e44f2dc14f3

Forked from ryanpitts/gist:1304725
Created December 17, 2018 10:45
Show Gist options
  • Save MewX/e3e0d5808ee24064d63f8e44f2dc14f3 to your computer and use it in GitHub Desktop.
Save MewX/e3e0d5808ee24064d63f8e44f2dc14f3 to your computer and use it in GitHub Desktop.
Download ZIP
GROUP BY and Select MAX from each group in Django, 2 queries
Raw
gistfile1.txt
'''
given a Model with:
category = models.CharField(max_length=32, choices=CATEGORY_CHOICES)
pubdate = models.DateTimeField(default=datetime.now)
<other fields>
Fetch the item from each category with the latest pubdate.
'''
model_max_set = Model.objects.values('category').annotate(max_pubdate=Max('pubdate')).order_by()
q_statement = Q()
for pair in model_max_set:
q_statement |= (Q(category__exact=pair['category']) & Q(pubdate=pair['max_pubdate']))
model_set = Model.objects.filter(q_statement)
@MewX
Copy link
Author

MewX commented Dec 17, 2018

When a filter is applied to the model_max_set, but no data found. This one will return every records.

A solution is to add filters on both model_max_set and model_set.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment