How to get the latest (distinct) records filtered by a non unique field in Django

dekomote 0
Viewed 35

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).

orm sql django python
3 Answers
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]
M.J.GH.PY0

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)

dekomote0

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.

Maxim Danilov0
Related