Django ORM: Filter by extra attribute

Question

I want to filter some database objects by a concatenated string.

The normal SQL query would be:

SELECT concat(firstName, ' ', name) FROM person WHERE CONCAT(firstName, ' ', name) LIKE "a%";

In the model, I have created a manager called PersonObjects:

class PersonObjects(Manager):
    attrs = { 
        'fullName': "CONCAT(firstName, ' ', name)"
    }   

    def get_query_set(self):
        return super(PersonObjects, self).get_query_set().extra(
            select=self.attrs)

I also configured this in my model:

objects = managers.PersonObjects()

Now accessing fullName works for single objects:

>>> p = models.Person.objects.get(pk=4)
>>> p.fullName
u'Fred Borminski'

But it does not work in a filter:

>>> p = models.Person.objects.filter(fullName__startswith='Alexei')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.7/site-packages/django/db/models/manager.py", line 141, in filter
    return self.get_query_set().filter(*args, **kwargs)
  File "/usr/lib/python2.7/site-packages/django/db/models/query.py", line 550, in filter
    return self._filter_or_exclude(False, *args, **kwargs)
  File "/usr/lib/python2.7/site-packages/django/db/models/query.py", line 568, in _filter_or_exclude
    clone.query.add_q(Q(*args, **kwargs))
  File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1128, in add_q
    can_reuse=used_aliases)
  File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1026, in add_filter
    negate=negate, process_extras=process_extras)
  File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1191, in setup_joins
    "Choices are: %s" % (name, ", ".join(names)))
FieldError: Cannot resolve keyword 'fullName' into field. Choices are: firstName, gender, name, (...)

Is this a bug or a feature? How can I fix this?

Thanks.

score 23 · Accepted Answer · answered Dec 03 '10 at 18:40

23

It's not a bug. filter() only inspects model definitions, so it doesn't recognize fullName as a declared field (because it's not - it's an extra argument in a query).

You can add the fullName to WHERE using extra():

Person.objects.extra(where=["fullName LIKE %s"], params=["Alexei%"])

answered Dec 03 '10 at 18:40

lqc

7,434
1
25
25

Unfortunately this doesn't work. It still complains about not finding the fullName attribute. Retrieving the fullName attribute from an object directly works though. Does this `extra` method somehow overwrite the previously set extra attributes from the manager? – Danilo Bargen Dec 09 '10 at 09:24
2

Actually this doesn't work either: `models.Person.objects.extra(select={'fullName': "CONCAT(firstName, ' ', name)"}, where=['fullName LIKE %s'], params=['Alexei%'])` (It throws "Unknown column 'fullName' in 'where clause'". – Danilo Bargen Dec 09 '10 at 09:29
10

I'm sorry for the triple comment. The reason for this behavior is that Django of course passes the fullName as an alias, which does not work with MySQL. It would work in a `HAVING`-clause, but that doesn't seem to be supported by Django. Instead, I'm using the following (not so beautiful) compromise: `models.Person.objects.extra(where=["CONCAT(firstName, ' ', name) LIKE %s"], params=['Alexei%'])`. Thanks for your answer. – Danilo Bargen Dec 09 '10 at 09:36
1

Hmm... I didn't know aliases don't work on MySQL. Good you managed to work it out. – lqc Dec 09 '10 at 16:44

score 1 · Answer 2 · edited May 23 '17 at 12:13

I solved this by implementing a custom Aggregate function. In this case I needed to concatenate individual fields into a street address to be able to filter/search for matches. The following aggregate function allows to specify a field and one or more others to perform a SQL CONCAT_WS.

Edit 3 Aug 2015:

A better implementation with details gleaned from https://stackoverflow.com/a/19529861/3230522. The previous implementation would fail if the queryset was used in a subquery. The table names are now correct, although I note that this just works for concatenation of columns from the same table.

from django.db.models import Aggregate
from django.db.models.sql.aggregates import Aggregate as SQLAggregate

class SqlAggregate(SQLAggregate):
    sql_function = 'CONCAT_WS'
    sql_template = u'%(function)s(" ", %(field)s, %(columns_to_concatenate)s)'

    def as_sql(self, qn, connection):
        self.extra['columns_to_concatenate'] = ', '.join(
        ['.'.join([qn(self.col[0]), qn(c.strip())]) for c in self.extra['with_columns'].split(',')])
        return super(SqlAggregate, self).as_sql(qn, connection)

class Concatenate(Aggregate):
    sql = SqlAggregate

    def __init__(self, expression, **extra):
        super(Concatenate, self).__init__(
            expression,
            **extra)

    def add_to_query(self, query, alias, col, source, is_summary):

        aggregate = self.sql(col,
                         source=source,
                         is_summary=is_summary,
                         **self.extra)

        query.aggregates[alias] = aggregate

score 0 · Answer 3 · answered Apr 24 '16 at 17:07

The proposed solution worked great with postgresql and JSONB fields in the code below. Only records that have the 'partner' key under the 'key' jsonb field are returned:

query_partner = "select key->>'partner' from accounting_subaccount " \
                "where accounting_subaccount.id = subaccount_id and key ? 'partner'"
qs = queryset.extra(select={'partner': query_partner}, where=["key ? 'partner'"])

Django ORM: Filter by extra attribute

3 Answers3

Linked