Django ORM. Joining subquery on condition

Question

I have a table TickerStatement, which contains financial statements about companies

class Statements(models.TextChoices):
    """
    Supported statements
    """
    capital_lease_obligations = 'capital_lease_obligations'
    net_income = 'net_income'
    price = 'price'
    total_assets = 'total_assets'
    short_term_debt = 'short_term_debt'
    total_long_term_debt = 'total_long_term_debt'
    total_revenue = 'total_revenue'
    total_shareholder_equity = 'total_shareholder_equity'


class TickerStatement(TimeStampMixin):
    """
    Model that represents ticker financial statements
    """
    name = models.CharField(choices=Statements.choices, max_length=50)
    fiscal_date_ending = models.DateField()
    value = models.DecimalField(max_digits=MAX_DIGITS, decimal_places=DECIMAL_PLACES)
    ticker = models.ForeignKey(Ticker, on_delete=models.CASCADE, null=False,
                               related_name='ticker_statements')

And now I'm trying to calculate a multiplier. The formula looks like:

(short_term_debt + total_long_term_debt) / total_shareholder_equity

I wrote a raw SQL query

SELECT "fin_tickerstatement"."fiscal_date_ending",
       t2.equity                             AS "equity",
       value                                 AS "debt",
       short_term_debt                       AS "short_term_debt",
       (value + short_term_debt) / t2.equity AS "result"
FROM "fin_tickerstatement"
         JOIN
     (SELECT "fin_tickerstatement"."fiscal_date_ending",
             fin_tickerstatement.value AS "equity"
      FROM "fin_tickerstatement"
      WHERE ("fin_tickerstatement"."ticker_id" = 12
          AND "fin_tickerstatement"."fiscal_date_ending" >= date'2015-09-03'
          AND "fin_tickerstatement"."name" = 'total_shareholder_equity')
      GROUP BY "fin_tickerstatement"."fiscal_date_ending",
               fin_tickerstatement.value
      ORDER BY "fin_tickerstatement"."fiscal_date_ending" DESC) t2
     ON fin_tickerstatement.fiscal_date_ending = t2.fiscal_date_ending
         JOIN
     (SELECT "fin_tickerstatement"."fiscal_date_ending",
             fin_tickerstatement.value AS "short_term_debt"
      FROM "fin_tickerstatement"
      WHERE ("fin_tickerstatement"."ticker_id" = 12
          AND "fin_tickerstatement"."fiscal_date_ending" >= date'2015-09-03'
          AND "fin_tickerstatement"."name" = 'short_term_debt')
      GROUP BY "fin_tickerstatement"."fiscal_date_ending",
               fin_tickerstatement.value
      ORDER BY "fin_tickerstatement"."fiscal_date_ending" DESC) t3
     ON fin_tickerstatement.fiscal_date_ending = t3.fiscal_date_ending
WHERE ("fin_tickerstatement"."ticker_id" = 12
    AND "fin_tickerstatement"."fiscal_date_ending" >= date'2015-09-03'
    AND "fin_tickerstatement"."name" = 'total_long_term_debt')
GROUP BY "fin_tickerstatement"."fiscal_date_ending",
         equity,
         debt,
         short_term_debt
ORDER BY "fin_tickerstatement"."fiscal_date_ending" DESC;

and have no idea how to translate it into Django ORM. Maybe you have some ideas or know some Django plugins that can help me.

Answer 1

The only way to solve this problem is to install django-query-builder.