Django Combined Filters

Question

I have 3 models similar to the ones below, and I'm trying to get the latest sale date for my items in one query, which is certainly possible using SQL, but I'm trying to use the Django built-in functionality:

class Item(models.Model):
    name = models.CharField()
    ...

class InventoryEntry(models.Model):
    delta = models.IntegerField()
    item = models.ForeignKey("Item")
    receipt = models.ForeignKey("Receipt", null=True)
    created = models.DateTimeField(default=timezone.now)
    ...

class Receipt(models.Model):
    amt = models.IntegerField()
    ...

What I'm trying to do is request my objects and annotate the last time a sale was made. An InventoryEntry model can be requested for whether a record was sold based on the availability of a receipt (inventory can also be adjusted due to ordering or stolen, etc., and I'm only interested in the last sale).

, . , , __isnull = False InventoryEntry:

Item.objects.filter(**item_qs_kwargs).annotate(latest_sale_date=Max('inventoryentry_set__created'))

" ", , , , .

Answer 1

:

from django.db.models import Max, Case, When, F

sale_date = Case(When(
    inventoryentry__receipt=None,
    then=None
), default=F('inventoryentry__created'))
qs = Item.objects.annotate(latest_sale_date=Max(sale_date))

Answer 2

- . .

from django.db.models import F

Item.objects\
.annotate(latest_inventoryentry_id=Max('inventoryentry__created'))\
.filter(
inventoryentry__id=F('latest_inventoryentry_id'), 
inventoryentry__receipt=None
)

. .