Django admin custom filter: ProgrammingError: more than one row returned by a subquery used as an expression

[kholidfu]

Hi, I'm trying this custom filter query for django admin

return queryset.extra(select = {'val': "select id from web_quote WHERE character_length(quote_text) < 30"})

and I got something like this:

Programming error: more than one row returned by a subquery used as an expression

Basically I'm trying to filter quote_text column which length of the string less than 30...

Any ideas?

[Camilo Torres]

Hi,

The problem with your approach is the SQL will actually be something like this:

select <model fields>,
(select id from web_quote WHERE character_length(quote_text) < 30) as val
from <model table>;

That will fail in RDMS like Postgres if there are more than one "quote_text" with length < 30.

May be you want is something like this:

select <model fields>
from <model table>
where character_length(quote_text) < 30

If that is the case, then you probably will need something like:

return queryset.extra(where=['character_length(quote_text) < 30'])

or even better:

from django.db.models.functions import Length
return quryset.annotate(quote_len=Length('quote_text')).filter(quote_len__lt=30)

Notice extra() is deprecated.