CODEWITHSUNDEEP
djangodjango-modelsforeign-keysaggregate
Vladimir Vislovich
Vladimir Vislovich

Reputation: 381

Django aggregate Min on filtered reverse foreign field

class Order(models.Model):
    ...

class OrderApplication(models.Model):
    created_at = models.DateTimeField(auto_now=True)
    driver = models.ForeignKey(Driver, on_delete=models.CASCADE)
    suggested_price = models.DecimalField(..., blank=True, null=True)

I have the models as described above. All Orders usually have more than 1 OrderApplication (OA). However, there is only 1 OA for each Order which has a non-null suggested_price field.
At some moments I have to serialize a list of Orders and send it to the front-end React app.
I want my orders to be placed in a very specific Order:

  1. For each order I want to get the OA which relates to it and have a non-null suggested_price field
  2. Then when I get this OA I want to check its created_at field
  3. Basing on this field the earlier the OA was created the closer to the end should the Order be placed. So, the first Order will be the one which has an OA with specified suggested_price and created as recently as possible

What I have already tried is ordering the Orders by the first created OA related to it:

.annotate(suggestion_created=Min('orderapplication__created_at')).order_by("-suggestion_created")

Here inside Min aggregator I use a reversed foreign relation of the Order which is can be accessed as orderapplication and get the first date of the objects which are in this reversed relation.
However, the problem is that it is not always true that the first OA related to any Order has suggested_price specified (even though it's the most common case), it may be the 2nd, the 3rd or something else.
I have also tried using filter parameter of the Min, however, it turns out that this filter is applied to the Orders themselves, but not to the reversed relation.
What I see as a possible solution is to use some aggregation function which can filter reversed relation, however, I haven't found one

Upvotes: 1

Views: 322

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476574

You can filter on the orderapplication as well:

from django.db.models import Q

Order.objects.annotate(
    suggestion_created=Min(
        'orderapplication__created_at',
        filter=Q(orderapplication__suggested_price__isnull=False)
    )
).order_by('-suggestion_created')

Or you can simply put this in the .order_by clause. If there is no such orderapplication the value will be NULL, you thus should decide if you want the NULLs to be first or last:

from django.db.models import Q

Order.objects.order_by(
    Min(
        'orderapplication__created_at',
        filter=Q(orderapplication__suggested_price__isnull=False)
    ).desc(nulls_last=True)
)

Upvotes: 2

Related Questions