python - 使用内连接将 SQL 转换为 Django orm

Question

我正在尝试将此 SQL 查询传递给 django ORM，但是我的内部连接有问题，请您帮帮我，谢谢

我尝试在另一个查询中使用一个查询，但我无法比较 GT 过滤器的注释

Model.objects.filter( 
    status=Model.FAILED,
).filter(
    created__gt=Model.objects.filter(
        status=Model.APPROVED,
        is_active=True,
    ).annotate(
        approved_created=F('created')
    ).values_list('approved_created')
).select_related('invoice').values_list('pk')

这是我要转换的 SQL

SELECT * FROM model IA1
INNER JOIN model IA2 ON IA1.invoice_id=IA2.invoice_id
WHERE IA1.status = 'FAILED'
AND IA2.status = 'APPROVED'
AND IA2.is_active = True
AND IA1.created > IA2.created;

模型定义

class Model(models.Model):

    APPROVED = 'APPROVED'
    FAILED = 'FAILED'
    IN_PROCESS = 'IN_PROCESS'
    PENDING = 'PENDING'

    STATUS_CHOICES = (
        (APPROVED, APPROVED),
        (FAILED, FAILED),
        (IN_PROCESS, IN_PROCESS),
        (PENDING, PENDING)
    )

    status = models.CharField(
        max_length=20,
        choices=STATUS_CHOICES,
        blank=False,
        null=False,
        default=PENDING
    )

    invoice = models.ForeignKey(
        Invoice,
        on_delete=models.PROTECT,
        blank=False,
        related_name='assignments'
    )

标签： pythonsqldjangopostgresqldjango-models