Django ORM left join subquery

У меня следующая модель:

class Transaction(models.Model):
    created_by_id = models.IntegerField()
    quantity = models.FloatField()
    transaction_type = models.SmalIntegerField()
    transaction_status = models.SmallIntegerField()
    related_transaction_id = models.IntegerField(
        blank=True,
        null=True,
    )

В настоящее время в таблице хранятся следующие данные:

Я хочу воспроизвести следующий SQL оператор в ORM Django (это точный вывод, который я ищу):

WITH pending_status AS (
    SELECT
        id AS pending_id,
        transaction_status AS pending_status
    FROM transaction
    WHERE transaction_status = 2
),
done_or_rejected AS (
    SELECT
        id AS related_id,
        related_transaction_id,
        transaction_status AS current_status
    FROM transaction
    WHERE transaction_status != 2
    AND transaction_type = 2
),
all_transactions AS (
    SELECT
        id AS original_id,
        created_by_id,
        quantity,
        transaction_status,
        transaction_type
    FROM transaction
)
SELECT
    original_id,
    created_by_id,
    quantity,
    related_id,
    coalesce(current_status, transaction_status) as status,
    transaction_type
FROM all_transactions
LEFT JOIN pending_status
    ON pending_status.pending_id = all_transactions.original_id
LEFT JOIN done_or_rejected
    ON done_or_rejected.related_transaction_id = pending_status.pending_id
    AND pending_status.pending_status != done_or_rejected.current_status
WHERE
    (related_id IS NULL AND transaction_status = 1 AND transaction_type != 2)
    OR (related_id IS NULL AND transaction_status = 2 AND transaction_type = 2)
    OR (related_id IS NOT NULL)
ORDER BY original_id DESC;

Который возвращает следующий вывод:

Эти три подзапроса я продублировал следующим образом:

pending_status = (
    Transaction
    .objects
    .filter(transaction_status=2)
    .values(
        pending_id=models.F("id"),
        pending_status=models.F("transaction_status"),
    )
)

done_or_rejected = (
    Transaction
    .objects
    .filter(~models.Q(transaction_status=2))
    .filter(transaction_type=2)
    .values(
        "related_transaction_id",
        related_id=models.F("id"),
        current_status=models.F("transaction_status"),
    )
)

all_transactions = (
    Transaction
    .objects
    .values(
        "created_by_id",
        "quantity",
        "transaction_status",
        "transaction_type",
        original_id=models.F("id"),
    )
)

Есть ли способ сделать два левых соединения на двух разных подзапросах? Как я могу перевести этот SQL-запрос в ORM Django?

Спасибо!

Вернуться на верх