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.

Answer 2

Easier:

from django.db.models import Q 

def find_user_by_name(query_name):
   qs = User.objects.all()
   for term in query_name.split():
     qs = qs.filter( Q(first_name__icontains = term) | Q(last_name__icontains = term))
   return qs

Where query_name could be "John Smith" (but would also retrieve user Smith John if any).

Answer 3

class User( models.Model ):
    first_name = models.CharField( max_length=64 )
    last_name = models.CharField( max_length=64 )
    full_name = models.CharField( max_length=128 )
    def save( self, *args, **kw ):
        self.full_name = '{0} {1}'.format( first_name, last_name )
        super( User, self ).save( *args, **kw )

Answer 4

I used this Query to search firstname, lastname, also the fullname.

It solved my problem.

from django.db.models import Q, F
from django.db.models import Value as V
from django.db.models.functions import Concat 

user_list = models.User.objects.annotate(
                        full_name=Concat('first_name', V(' '), 'last_name')
                    ).filter(   
                        Q(full_name__icontains=keyword) | 
                        Q(first_name__icontains=keyword) | 
                        Q(last_name__icontains=keyword)
                    )

Answer 5

Unless I'm missing something, you can use python to query your DB like so:

from django.contrib.auth.models import User
x = User.objects.filter(first_name='John', last_name='Smith') 

Edit: To answer your question:

If you need to return 'John Paul Smith' when the user searches for 'John Smith' then you can use 'contains' which translates into a SQL LIKE. If you just need the capacity to store the name 'John Paul' put both names in the first_name column.

User.objects.filter(first_name__contains='John', last_name__contains='Smith') 

This translates to:

SELECT * FROM USERS
WHERE first_name LIKE'%John%' 
AND last_name LIKE'%Smith%'

Answer 6

What about this:

query = request.GET.get('query')
users = []

try:
    firstname = query.split(' ')[0]
    lastname  = query.split(' ')[1]
    users += Users.objects.filter(firstname__icontains=firstname,lastname__icontains=lastname)
    users += Users.objects.filter(firstname__icontains=lastname,lastname__icontains=firstname)

users = set(users)

Tried and tested!

Answer 7

I would use the Django method get_full_name() on User model. There is no need to create a column on the Database.

See here for more info: https://docs.djangoproject.com/en/4.1/ref/contrib/auth/#django.contrib.auth.models.User.get_full_name

Then you could do something like this in your templates:

{{ myUser.get_full_name|default:myUser.username }}

As first_name and last_name are not required on the User model, it could return empty. Then this way you can have a default of username if they are.