|given a Model with:|
|category = models.CharField(max_length=32, choices=CATEGORY_CHOICES)|
|pubdate = models.DateTimeField(default=datetime.now)|
|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)|
I went with:
@Phlip In your filter operation your only check is that the model instance has the same created_at date as one of the values in the list. But wouldn't this break down if any instance from one category had the same date/datetime as the max for a different category? It would be included even if it wasn't the max for its category.
So even though the pubdate for id=1 is less than id=2, it will be included because it has the same pubdate as id=4. IDs 1, 2 and 4 will all be included.
I guess if you have a datetime (as opposed to date, or some other less granular value) you may feel more confident that they wouldn't share the exact same datetime, but I think it's worth pointing out this assumption/limitation.
Thanks @ns5d for posting this line here. Learning django and this made my CBV work perfectly in my use. Was fiddling with a less than ideal solution before, but this was worked beautifully. (did need to finally move my dev db from sqlite to postgres for distinct functionality)
You can use sub-queries to achieve the same thing in a single query as follows:
sq = Item.objects.filter(category=OuterRef('category')).order_by('-pubdate') # deferred execution Item.objects.filter(pk=Subquery(sq.values('pk')[:1]))
Alternatively, If you make Category a model like:
category = models.ForeignKey(Category, on_delete=models.SET_NULL, related_name='items') pubdate = models.DateTimeField(default=datetime.now)
You can then do it in one query irrespective of database backend as follows:
I haven't tested which of them is faster, but both execute a single query on the database backend and work with every backend supported by Django, including SQLite.