How to align two columns of a django model table

Question

I search in my project what I want is concat for the column in where where to get the results from the table.

That's what I'm 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, for example:

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

But when I add this condition to the query set, 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 to an OR condition?

Answer 1

Advanced filters can be resolved by Q () and query expressions such as Func (), Value (), and F (). The only trick used is the rhs_only Custom Search , which uses the right side of the search and ignores the left side, because it’s easier to use all concatenated fields right on the right side. The unforgettable function concat_like encapsulates everything that is easy to use 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 expressions and related objects are supported by this code. All arguments are properly escaped.

Usage example:

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

Answer 2

Relevant documentation:

• https://docs.djangoproject.com/en/1.11/ref/models/expressions/#func-expressions
  • See also ... Value() and F()
• https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#aggregations-and-other-queryset-clauses

You can reference annotated fields inside the filter method. Thus, you can filter out two concatenated fields and add them as another OR condition, for example:

 from django.db.models import F, Func, Value # Because we added user_full_name as an annotation below, # we can refer to it in the filters 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]']) | Q(user_full_name__icontains=request.POST['search[value]']) # <------ ]) # Add the annotation to your queryset # I'm not actually sure what the related_name or field_name for your user # profiles are, so I'm pretending that tickets have a profile foreignkey field # to where the first_name and last_name fields are user_full_name_expr = Func(Value(' '), F('profile__first_name'), F('profile__last_name'), function='CONCAT_WS') # The next two lines can be combined as long as the annotation comes first. tickets = Ticket.objects.annotate(user_full_name=user_full_name_expr) tickets = tickets.get_active(u, page_type).filter(*q).exclude(*exq).order_by(*order_dash)[cur:cur_length] 

---

For fun, here is a working example based on the User model.

 from django.contrib.auth.models import User from django.db.models import F, Func, Value User.objects.create(username='john', first_name='John', last_name='Jingleheimer-Schmidt') User.objects.create(username='mike', first_name='Michael', last_name='Finnigan') foo = User.objects.annotate(full_name=Func(Value(' '), F('first_name'), F('last_name'), function='CONCAT_WS')) print(foo.filter(full_name__icontains='john')) # outputs: [<User: john>] 

Answer 3

What you need to create FullText. I recommend using ( http://haystacksearch.org/ )

See the Django documentation ( https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/search/ )