CODEWITHSUNDEEP
sqldjangopostgresqldjango-queryset
Pablo Vallejo
Pablo Vallejo

Reputation: 201

Django queryset: Filter by reverse key and order by

How can I filter models of Procedure that have their last Status in '1' using procedurestatus_set.

class Procedure(models.Model):
    name = models.CharField(
        max_length=256,
    )

Class Status(models.Model)
    procedure = models.ForeignKey(
       Procedure,
    ) 

    status = models.CharField(
        max_length=256,
    )

Procedure.objects.filter(status__status='1')

In the example above, we can get Procedures that have any Status (procedurestatus_set) with status 1, but not those whose last Status.status is 1.

How can can I filter Procedure by verifying their last Status?

Procedure.objects.filter(status__last__status='1')

I'm using Django 1.9.

Upvotes: 2

Views: 572

Answers (1)

user2390182
user2390182

Reputation: 73450

Assuming 'last' means largest pk and you want to query for all Procedure instances whose last Status has status==1, postgres allows you the following:

last_statuses=Status.objects.filter(
    id__in=Status.objects.order_by('procedure_id', '-pk').distinct('procedure_id')
).filter(status=1)

procedures = Procedure.objects.filter(id__in=last_statuses.values('procedure_id'))

In order for the two Filters you need in the Status query not to be merged into one filter expression when the db is actually accessed (which django will do), you have to use this nested expression with id__in.

In the case at hand, the nested (inner) order_by filter groups Status by procedure_id and sorts them by descending pk, the distinct selects the first (largest pk) Status for each Procedure (Note: distinct with field arguments is only supported by Postgres with some caveats). Then you can apply any other filter to the outer QuerySet.

The Django ORM should translate the whole into one query to the database.

Upvotes: 1

Related Questions