I'll demonstrate by using an example. This is the model (the primary key is implicit):
class Item(models.Model):
sku = models.CharField(null=False)
description = models.CharField(null=True)
I have a list of skus, I need to get the latest descriptions for all skus in the filter list that are written in the table for the model Item. Latest item == greatest id.
I need a way to annotate the latest description per sku:
Item.objects.values("sku").filter(sku__in=list_of_skus).annotate(latest_descr=Latest('description').order_by("-id")
but this won't work for various reasons (excluding the missing aggregate function).
CodePudding user response:
Item.objects.values("sku").filter(sku__in=list_of_skus).annotate(latest_descr=Latest('description').lastest("-id")
Or use this
Item.objects.values("sku").filter(sku__in=list_of_skus).annotate(latest_descr=Latest('description').order_by("-id").reverse()[0]
CodePudding user response:
I used postgres ArrayAgg aggregate function to aggregate the latest description like so:
from django.contrib.postgres.aggregates import ArrayAgg
class ArrayAggLatest(ArrayAgg):
template = "(%(function)s(%(expressions)s ORDER BY id DESC))[1]"
Item.objects.filter(sku__in=skus).values("sku").annotate(descr=ArrayAggLatest("description"))
The aggregate function aggregates all descriptions ordered by descending ID of the original table and gets the 1st element (0 element is None)
CodePudding user response:
Answer from @M.J.GH.PY or @dekomote war not correct. If you have a model:
class Item(models.Model):
sku = models.CharField(null=False)
description = models.CharField(null=True)
this model has already by default order_by= 'id',
You don't need annotate something. You can:
get the last object:
Item.objects.filter(sku__in=list_of_skus).last()
get the last value of description
:
Item.objects.filter(sku__in=list_of_skus).values_list('description', flat=True).last()
Both variants give you a None
if a queryset
is empty.