在Django中进一步过滤queryset的结果

Question

我在django中有两个基本模型，分别名为Applications和Status，还有一个用于跟踪应用程序状态的模型，名为TrackApplicationStatus。因此，每当应用程序的状态发生变化时，就会向TrackApplicationStatus表中添加一条新记录。每个应用程序的当前状态是该表的最新记录的状态

这些模型的定义如下：

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']

在第一步中，我使用以下django查询检索每个应用程序的当前状态：

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

生成的SQL查询如下所示：

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

我接下来要做的是获取所有具有特定状态的应用程序。在SQL术语中，我将对上述结果表应用WHERE子句，如下所示：

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

如何使用django ORM获取相同的数据

Answer 1

你不需要3个模型。这可以简单地在1个模型中完成

如下定义您的模型：

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']

现在，您只需根据应用程序的状态筛选它们：

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

Answer 2

我找到了一个不太好的方法：

# 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')))

相应的SQL为：

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