“Unexpected ORM Query Translation: CAST and Unnest Functions in Django”

Question

I'm trying to execute a query using Django ORM to obtain the following SQL query:

python: 3.9.16
django: 3.2.19
value = text[]

SELECT CAST(x AS float)
FROM public.collection_basic_arrays
CROSS JOIN unnest(value) AS t(x)
WHERE x::float BETWEEN 0.001 AND 0.01

However, the ORM is translating it to the following SQL query:

SELECT CAST(unnest("collection_basic_arrays"."value") AS double precision) AS "value_unnest" 
FROM "collection_basic_arrays" 
WHERE 
(CAST(unnest("collection_basic_arrays"."value") AS double precision) > 0.001 
 AND CAST(unnest("collection_basic_arrays"."value") AS double precision) <= 0.01)


ERROR:  set-returning functions are not allowed in WHERE
LINE 5:  AND CAST(unnest("collection_basic_arrays"."value") AS doubl...
                  ^ 

SQL state: 0A000
Character: 219

I'm using a custom function and Django built-in functions to execute the query. I'm trying to obtain the query result with the following function:

from django.db.models.functions import Cast
from django.db import models
from django.db.models import Func, Q

class Unnest(Func):
    function = 'unnest'

def execute(self):
    value_key = 'value'
    annotate = {}
    filter = {}
    annotate[value_key + '_unnest'] = Unnest(value_key)
    value_key += '_unnest'

    if value_key in annotate:
        conversion = Cast(annotate[value_key], models.FloatField())
        if conversion is not None:
            annotate[value_key] = conversion

    else:
        conversion = Cast(value_key, models.FloatField())
        if conversion is not None:
            annotate[value_key] = conversion
            value_key += '_conv'

    filter[value_key + '__gt'] = 0.001
    filter[value_key + '__lte'] = 0.01
    data = MyModel.objects.annotate(**annotate).filter(Q(**filter)).values(value_key)

    query_str = str(data.query)

    return data

Is there any way to obtain the expected SQL query using Django ORM? I'd appreciate any suggestions or solutions you may have.

Thank you very much.

“Unexpected ORM Query Translation: CAST and Unnest Functions in Django”

0 Answers0