Django Queryset for concat query fullname of first_name and last_name

Question

I would like to do a fullname (first_name concat last_name) search/query in Django.

The following is my model:

class Employee(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, related_name='employee')
    company = models.ForeignKey(Company)
    username = models.CharField(max_length=30, blank=False)    
    first_name = models.CharField(max_length=30, blank=False)
    last_name = models.CharField(max_length=30, blank=False)    
    created_at = models.DateTimeField(auto_now_add=True)

For example we have an entry like this. first_name:"Michael", last_name:"Jackson"

I want to be able to query full name "michael jackson". If can show without case sensitive would be great as well.

This other stackoverflow question is similar but the answers is not fulfilling this particular requirement in proper Querying full name in Django. We want to be able to do a concat search.

I tried with annotate but it doesnt work

queryset = Employee.objects.annotate(fullname=Concat('first_name', 'last_name'))
search_result = queryset.filter(fullname__icontains='michael jackson')

score 13 · Answer 1 · answered Dec 09 '17 at 00:22

13

this worked.

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

queryset = Employee.objects.annotate(fullname=Concat('first_name', Value(' '), 'last_name'))
c = queryset.filter(fullname__icontains='michael jackson')

answered Dec 09 '17 at 00:22

Axil

3,606
10
62
136

3

In This case, If middle_name is present there, then ```queryset = Employee.objects.annotate(fullname=Concat('first_name', Value(' '), 'middle_name', Value(' '), 'last_name',))``` here, if middle name blank, then the value will be 'michael jackson'. It will not be searched then. As two spaces includes here. @Axil – Chandan Sharma Nov 11 '19 at 13:21

Django Queryset for concat query fullname of first_name and last_name

1 Answers1