Django queryset filter after a concatenation of two columns

Question

Is there any way to filter a model using a concatenation of two of its columns? My model is like this:

class Item(models.Model):
    series = models.CharField(max_length=50)
    number = models.CharField(max_length=50)

What I need is to filter after the concatenation of the two columns, if a user inputs A123 I want to be able to find any Item that has series and number like %A and 123% or %A1 and 23% Is this possible using the django models? Or is it possible with raw sql? I would rather not construct a new column with the concatenation.

score 44 · Answer 1 · answered Mar 25 '16 at 16:56

44

In addition to what was said earlier, example:

from django.db.models import Value
from django.db.models.functions import Concat
queryset = Item.objects.annotate(search_name=Concat('series', Value(' '), 'number'))
# then you can filter:
queryset.filter(search_name__icontains='whatever text')

answered Mar 25 '16 at 16:56

bilabon

541
4
3

It's a function you need to use if you want to use an actual string instead of a column name – mccc Apr 06 '17 at 10:29
This works for me. But, I'm also trying to `order_by('search_name')`. The query executes without error, but the items are not ordered correctly. How can I order the results in alphabetical order by the annotated field, `search_name`? – MikeyE Jul 17 '18 at 14:23
@MikeyE: do you got an answer on your question? if yes, please share ;) – cwhisperer Feb 18 '19 at 10:21
@mikeyE @cwhisperer The documentation on annotations (first link in my answer) explicitly says that `order_by` is entirely allowed (just a couple of paragraphs below the linked anchor), without listing any drawbacks, so your issue is probably somewhere else. – mccc Dec 18 '20 at 10:52

score 34 · Accepted Answer · answered Sep 14 '15 at 07:22

34

Yes that is possible; you will need to annotate the QuerySet with the concatenation of the fields, and that new "virtual" column will be capable of filtering.

relevant documentation on filtering annotations

using Concat as an annotation function

answered Sep 14 '15 at 07:22

mccc

2,354
1
20
22

Thanks for the answer, sadly I am using django 1.3 so there is no Concat defined. I tried doing it like described here: http://stackoverflow.com/questions/4348124/django-orm-filter-by-extra-attribute/4348728#4348728 but I get some errors and I also tired doing it like this: invoices = invoices.extra( where=['CONCAT(document_series_name, document_number) LIKE "%F001%"'], ) but I got the same erros: tuple index out of range /usr/local/lib/python2.7/dist-packages/django/db/backends/postgresql_psycopg2/base.py in execute, line 44 – Virgil Balibanu Sep 15 '15 at 08:24
1

Well that's a version number I haven't heard in a while... The documentation is not even available anymore! I'm assuming you cannot upgrade for whatever reason - even if you really should, at least for security reasons. Anyway, your best bet is then probably writing raw SQL, maybe check whether `F()` expressions were available back then too. – mccc Sep 15 '15 at 11:40
Thanks, I will try, the answer is still valid thou, thank you – Virgil Balibanu Sep 15 '15 at 12:42

Ozan Honamlioglu · Answer 3 · 2017-03-08T14:46:14.073

I found my way, if you are gonna use some of ajax requests, then I started to use like this

in views.py

AllUser = User.objects.all()
users = []
for i in AllUser:
    if query in i.get_full_name():
        users += [{'first_name':i.first_name,'last_name':i.last_name,'full_name':i.get_full_name()}]

qUser = users

and in returned ajax page (in my case 'searchajax.html')

{% if qUser %}
  {% for i in qUser %}
    <p class="queryP">{{ i.full_name }}</p>
  {% endfor %}
{% endif %}

it works very well for me :))

Another way is using annotate

from django.db.models import CharField, Value as V
from django.db.models.functions import Concat
author =  User.objects.annotate(screen_name=Concat('first_name', V(' ') ,'last_name'))
for i in author:
    print i.screen_name

it makes the same job too :))

Don Kirkby · Answer 4 · 2016-07-05T17:24:36.130

Here's a full example that shows how to filter based on the annotate() function and a Concat expression.

# Tested with Django 1.9.2
import sys

import django
from django.apps import apps
from django.apps.config import AppConfig
from django.conf import settings
from django.db import connections, models, DEFAULT_DB_ALIAS
from django.db.models.base import ModelBase
from django.db.models.functions import Concat

NAME = 'udjango'


def main():
    setup()

    class Item(models.Model):
        series = models.CharField(max_length=50)
        number = models.CharField(max_length=50)

    syncdb(Item)

    Item.objects.create(series='A', number='1234')
    Item.objects.create(series='A', number='1230')
    Item.objects.create(series='A', number='9999')
    Item.objects.create(series='B', number='1234')

    print(Item.objects.annotate(
        search=Concat('series', 'number')).filter(
            search__icontains='A123').values_list('series', 'number'))
    # >>> [(u'A', u'1234'), (u'A', u'1230')]


def setup():
    DB_FILE = NAME + '.db'
    with open(DB_FILE, 'w'):
        pass  # wipe the database
    settings.configure(
        DEBUG=True,
        DATABASES={
            DEFAULT_DB_ALIAS: {
                'ENGINE': 'django.db.backends.sqlite3',
                'NAME': DB_FILE}},
        LOGGING={'version': 1,
                 'disable_existing_loggers': False,
                 'formatters': {
                    'debug': {
                        'format': '%(asctime)s[%(levelname)s]'
                                  '%(name)s.%(funcName)s(): %(message)s',
                        'datefmt': '%Y-%m-%d %H:%M:%S'}},
                 'handlers': {
                    'console': {
                        'level': 'DEBUG',
                        'class': 'logging.StreamHandler',
                        'formatter': 'debug'}},
                 'root': {
                    'handlers': ['console'],
                    'level': 'WARN'},
                 'loggers': {
                    "django.db": {"level": "WARN"}}})
    app_config = AppConfig(NAME, sys.modules['__main__'])
    apps.populate([app_config])
    django.setup()
    original_new_func = ModelBase.__new__

    @staticmethod
    def patched_new(cls, name, bases, attrs):
        if 'Meta' not in attrs:
            class Meta:
                app_label = NAME
            attrs['Meta'] = Meta
        return original_new_func(cls, name, bases, attrs)
    ModelBase.__new__ = patched_new


def syncdb(model):
    """ Standard syncdb expects models to be in reliable locations.

    Based on https://github.com/django/django/blob/1.9.3
    /django/core/management/commands/migrate.py#L285
    """
    connection = connections[DEFAULT_DB_ALIAS]
    with connection.schema_editor() as editor:
        editor.create_model(model)

main()

Django queryset filter after a concatenation of two columns

4 Answers4

Linked