[Fixed]-Django ORM: Filter by extra attribute


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


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__(

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

        aggregate = self.sql(col,

        query.aggregates[alias] = aggregate


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

Leave a comment