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.

Solution 1

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

Solution 2

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

Solution 3

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 :))

Solution 4

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

View more solutions