python - How to select from a subquery in Django's ORM?

Question

I want to select all rows for which bet_index is not NULL:

BetHistory.objects.values('account_id').annotate(
    bet_index=models.Case(models.When(
        models.Q(event_id=1, market_id=2) & (models.Q(account_id=3) | models.Q(account_id=4)),
        then=0),
    default=None,
    output_field=models.IntegerField())
).exclude(bet_index=None)

that yields the following SQL query

SELECT "bet_history"."account_id",
       CASE
         WHEN ( "bet_history"."event_id" = 1
                AND "bet_history"."market_id" = 2
                AND ( "bet_history"."account_id" = 3
                       OR "bet_history"."account_id" = 4 ) ) THEN 0
         ELSE NULL
       end AS "bet_index"
FROM   "bet_history"
WHERE  NOT ( CASE
               WHEN ( "bet_history"."event_id" = 1
                      AND "bet_history"."market_id" = 2
                      AND ( "bet_history"."account_id" = 3
                             OR "bet_history"."account_id" = 4 ) ) THEN 0
               ELSE NULL
             end IS NULL ) 

but there the case is repeated, I would like to have the following query:

SELECT *
FROM   (SELECT "bet_history"."account_id",
               CASE
                 WHEN ( "bet_history"."event_id" = 1
                        AND "bet_history"."market_id" = 2
                        AND ( "bet_history"."account_id" = 3
                               OR "bet_history"."account_id" = 4 ) ) THEN 0
                 ELSE NULL
               end AS "bet_index"
        FROM   "bet_history") AS "subquery"
WHERE  "subquery"."bet_index" IS NOT NULL;

so I need a select from subquery, how to do it in Django? Thanks.

question from:https://stackoverflow.com/questions/66049474/how-to-select-from-a-subquery-in-djangos-orm

Answer 1

Waitting for answers