python - Querying full name in Django

Question

How can I query on the full name in Django?

To clarify, I essentially want to do create a temporary column, combining first_name and last_name to give a fullname, then do a LIKE on that, like so:

select [fields] from Users where CONCAT(first_name, ' ', last_name) LIKE '%John Smith%";

The above query would return all users named John Smith. If possible I'd like to avoid using a raw SQL call.

The model I'm talking about specifically is the stock django.contrib.auth.models User model. Making changes to the model directly isn't a problem.

For example, if a user was to search for 'John Paul Smith', it should match users with a first name of 'John Paul' and last name 'Smith', as well as users with first name 'John' and last name 'Paul Smith'.

Answer 1

This question was posted long time ago, but I had the similar problem and find answers here pretty bad. The accepted answer only allows you to find exact match by first_name and last_name. The second answer is a little bit better but still bad because you hit database as much as there was words. Here's my solution that concatenates first_name and last_name annotates it and search in this field:

from django.db.models import Value as V
from django.db.models.functions import Concat   

users = User.objects.annotate(full_name=Concat('first_name', V(' '), 'last_name')).
                filter(full_name__icontains=query)

For example if the name of the person is John Smith, you can find him by typing john smith, john, smith, hn smi and so on. It hits database only ones. And I think this will be the exact SQL that you wanted in the open post.