Django ORM: LEFT JOIN condition based on another LEFT JOIN

Question

I'm using Django 4.0 and PostgreSQL (13.7) as the backend (upgrading would be possible if its required for a solution)

I have two models: Cat and Attribute. The Attribute holds generic key-value pairs describing Cat instances.

Cat table:

PK	name
1	Wanda
2	Aisha
3	Thala

Attribute table:

PK	cat_id	key	value
1	1	size	10
2	1	age	5
3	2	size	7
4	2	intelligence	75
5	2	children	3
6	3	intelligence	60
7	3	age	9

I'd like to select different attribute values depending on conditions of other attributes for all instances, e.g.:

Get size of a Cat if its age is greather than 4 or it has more than 2 children - or if there is no match, get age if intelligence is over 50.

Well, the example here contains random attributes and numbers and does not make any sense, but in my application's world the conditions can be overly complex including several recursive AND, OR, EXISTS and NOT conditions.

My query would be:

SELECT
  DISTINCT(cat.id),
  cat.name,
  COALESCE(
    result1.key,
    result2.key
  ) as result_key
  COALESCE(
    result1.value,
    result2.value
  ) as result_value
FROM cat
-- first condition
LEFT OUTER JOIN attribute cond1 ON (
   cat.id = cond1.cat_id AND (
    cond1.key = 'age' AND cond1.value > 4 OR cond1.key = 'children' and cond1.value > 2
  )
-- second condition
LEFT OUTER JOIN attribute cond2 ON (
   cat.id = cond2.cat_id AND (
    cond2.key = 'intelligence' AND cond2.value > 50
  )
)
-- choose the one or other attribute depending on the first two conditions
LEFT OUTER JOIN attribute result1 ON (
  cat.id = result1.cat_id AND (cond1.cat_id IS NOT NULL) AND result1.key = 'size'
)
LEFT OUTER JOIN attribute result2 ON (
  cat.id = result2.cat_id AND (cond2.cat_id IS NOT NULL) AND result2.key = 'intelligence'
)

The result should be:

PK	name	result_key	result_value
1	Wanda	size	10
2	Aisha	size	7
3	Thala	age	9

---

Using Django's ORM I tried the following:

Cat.objects.annotate(
    cond1=FilteredRelation(
        "attribute",
        condition=(
            Q(attribute__key="age", attribute__value__gt=4)
            | Q(attribute__key="children", attribute__value__gt=2)
        ),
    ),
    cond2=FilteredRelation(
        "attribute",
        condition=(Q(attribute__key="intelligence", attribute__value__gt=50)),
    ),
    result1=FilteredRelation(
        "attribute",
        condition=(Q(cond1__cat_id__isnull=False, attribute__key="size")),
    ),
    result2=FilteredRelation(
        "attribute",
        condition=(Q(cond2__cat_id__isnull=False, attribute__key="age")),
    ),
    result_key=Coalesce(F("result1__key"), F("result2__key")),
    result_value=Coalesce(F("result1__value"), F("result2__value")),
).distinct("pk")

It will fail with the following error:

ValueError: FilteredRelation's condition doesn't support
relations outside the 'attribute' (got 
'cond1__cat_id__isnull').

Is it somehow possible to construct this or any other query to get the expected results or is this a hard limitation of Django and only using a raw query is the last resort to resolve this issue?

Answer 1

I've ended up using CASE/WHEN statements within the SQL as it seems that the usecase described in my question is not supported by Django (as stated by the error message).

The resulting ORM query would be:

Cat.objects.annotate(
    cond1=FilteredRelation(
        "attribute",
        condition=(
            Q(attribute__key="age", attribute__value__gt=4)
            | Q(attribute__key="children", attribute__value__gt=2)
        ),
    ),
    cond2=FilteredRelation(
        "attribute",
        condition=(Q(attribute__key="intelligence", attribute__value__gt=50)),
    ),
    result1=FilteredRelation(
        "attribute",
        condition=(Q(attribute__key="size")),
    ),
    result2=FilteredRelation(
        "attribute",
        condition=(Q(attribute__key="age")),
    ),
    result=Case(
        When(cond1__cat_id__isnull=False, then=F("result1__value"))
        When(cond2__cat_id__isnull=False, then=F("result2__value"))
    ),
).distinct("pk")