Further filter the results of queryset in django-CodePudding

I have two basic models in django named Applications and Status and a third one to track the application statuses, named TrackApplicationStatus. So, every time the status of the application changes, a new record is added to the TrackApplicationStatus table. The current status of each application is the status of the most recent record of that table.

The models are defined as:

from django.db import models


class Application(models.Model):

    name = models.CharField(max_length=100)


class Status(models.Model):

    name = models.CharField(max_length=100, blank=False, unique=True)


class TrackApplicationStatus(models.Model):

    created_at = models.DateTimeField(auto_now_add=True)
    application = models.ForeignKey(Application, on_delete=models.CASCADE, related_name='statuses')
    status = models.ForeignKey(Status, on_delete=models.SET_NULL, related_name='applications_status', null=True)

    class Meta:
        get_latest_by = ['created_at']
        ordering = ['-created_at']

On the first step, I am retrieving the current statuses of each application with the following django query:

current_statuses = TrackApplicationStatus.objects.order_by('application_id', '-created_at').distinct('application_id')

The produced SQL query is the following:

SELECT 
DISTINCT ON ("api_trackapplicationstatus"."application_id")
"api_trackapplicationstatus"."id", 
"api_trackapplicationstatus"."created_at", 
"api_trackapplicationstatus"."application_id", 
"api_trackapplicationstatus"."status_id" 

FROM "api_trackapplicationstatus" 

ORDER BY 
"api_trackapplicationstatus"."application_id" ASC, "api_trackapplicationstatus"."created_at" DESC

What I want to do next, is to get all applications with a specific status. In SQL terms I would apply a WHERE clause on the above result table as follows:

SELECT sub.application_id

FROM
(
    SELECT DISTINCT ON ("api_trackapplicationstatus"."application_id") 
    "api_trackapplicationstatus"."created_at",
    "api_trackapplicationstatus"."application_id" as application_id,
    "api_trackapplicationstatus"."status_id" as status
    
    FROM "api_trackapplicationstatus"
    ORDER BY "api_trackapplicationstatus"."application_id" ASC,
                "api_trackapplicationstatus"."created_at" DESC
) AS sub

WHERE sub.status = 3

How can I acchive the same using the django ORM?

CodePudding user response：

You don't need 3 models. This can be simply done in 1 model.

Define your model like this:

class Application(model.Model):
    name = model.CharField(max_length=100)
    status = models.CharField(max_length=100, blank=False, unique=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        get_latest_by = ['created_at']
        ordering = ['-created_at']

Now you can simply filter the applications based on their status:

Applications.objects.filter(status='3').order_by('-created_at')

CodePudding user response：

I found a not so good way:

# The desired status
status=Status.objects.get(id=3)
# The current tracking item of each application
current_statuses = TrackApplicationStatus.objects.order_by('application_id', '-created_at').distinct('application_id')
# The tracking items with the current status 3
TrackApplicationStatus.objects.filter(status=status, id__in=Subquery(current_statuses.values('id')))

The corresponding SQL is:

SELECT "api_trackapplicationstatus"."id", 
"api_trackapplicationstatus"."created_at", 
"api_trackapplicationstatus"."application_id", 
"api_trackapplicationstatus"."status_id" 

FROM "api_trackapplicationstatus" 

WHERE ( 
       "api_trackapplicationstatus"."id" 
       IN (
           SELECT DISTINCT ON (U0."application_id") U0."id" 
           FROM "api_trackapplicationstatus" U0 
           ORDER BY U0."application_id" ASC, 
           U0."created_at" DESC
       ) 
       AND "api_trackapplicationstatus"."status_id" = 3
      ) 
ORDER BY "api_trackapplicationstatus"."created_at" DESC