How to concat two columns of table django model

Question

I am implementing search in my project what I want is to concat to column in where clause to get results from table.

Here is what I am doing:

from django.db.models import Q

if 'search[value]' in request.POST and len(request.POST['search[value]']) >= 3:
    search_value = request.POST['search[value]'].strip()

    q.extend([
        Q(id__icontains=request.POST['search[value]']) |
        (Q(created_by__first_name=request.POST['search[value]']) & Q(created_for=None)) |
        Q(created_for__first_name=request.POST['search[value]']) |
        (Q(created_by__last_name=request.POST['search[value]']) & Q(created_for=None)) |
        Q(created_for__last_name=request.POST['search[value]']) |
        (Q(created_by__email__icontains=search_value) & Q(created_for=None)) |
        Q(created_for__email__icontains=search_value) |
        Q(ticket_category=request.POST['search[value]']) |
        Q(status__icontains=request.POST['search[value]']) |
        Q(issue_type__icontains=request.POST['search[value]']) |
        Q(title__icontains=request.POST['search[value]']) |
        Q(assigned_to__first_name__icontains=request.POST['search[value]']) |

    ])

Now I want to add another OR condition like:

CONCAT(' ', created_by__first_name, created_by__last_name) like '%'search_value'%'

But when I add this condition to the queryset it becomes AND

where = ["CONCAT_WS(' ', profiles_userprofile.first_name, profiles_userprofile.last_name) like '"+request.POST['search[value]']+"' "]
            tickets = Ticket.objects.get_active(u, page_type).filter(*q).extra(where=where).exclude(*exq).order_by(*order_dash)[cur:cur_length]

How do I convert this into an OR condition?

Answer 1

Advanced filters can be solved by Q() object and Query expressions like Func(), Value() and F(). The only used trick is a Custom Lookup "rhs_only" that uses the right-hand-side of the lookup and ignores the left side, because it is easier to use all concatenated fields directly on the right side. A memorable function concat_like encapsulates that all to be easily used in queries.

from django.db.models import F, Func, Lookup, Q, Value
from django.db.models.fields import Field

def concat_like(columns, pattern):
    """Lookup filter: CONCAT_WS(' ', column_0, column_1...) LIKE pattern"""
    lhs = '%s__rhs_only' % columns[0]
    expr = Func(*(F(x) for x in columns), template="CONCAT_WS(' ', %(expressions)s)")
    return Q(**{lhs: Like(expr, Value(pattern))})

class Like(Func):
    def as_sql(self, compiler, connection):
        arg_sql, arg_params = zip(*[compiler.compile(x) for x in self.source_expressions])
        return ("%s LIKE '%s'" % tuple(arg_sql)), arg_params[0] + arg_params[1]

@Field.register_lookup
class RhsOnly(Lookup):
    """Skip the LHS and evaluate the boolean RHS only"""
    lookup_name = 'rhs_only'

    def as_sql(self, compiler, connection):
        return self.process_rhs(compiler, connection)

All boolean expression and related objects are supported by this code. All arguments are correctly escaped.

Example usage:

>>> qs = MyModel.objects.filter(Q(id=1) | concat_like(('first_name', 'surname'), 'searched'))
>>> str(qs.query)   # sql output simplified here
"SELECT .. WHERE id=1 OR (CONCAT_WS(' ', first_name, surname) LIKE 'searched')"