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.

Gere

Answer 1

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%"])

Heavyset

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? – Gere

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'". – Gere

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. – Gere

Hmm... I didn't know aliases don't work on MySQL. Good you managed to work it out. – Heavyset

Answer 2

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://mcmap.net/q/473430/-django-aggregate-multiple-columns-after-arithmetic-operation. 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

Scarf

Answer 3

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'"])

Fairy