Searching name and surname in Django

Question

I have this model on Django:

class Profile(models.Model):
  name = models.CharField(max_length=50, blank = False)
  surname = models.CharField(max_length=100, blank = False)
  ...

For example, I have 2 profiles in the db:

John Doe
John Smith

I want to do a form search, that searches in both name and surname attributes.

I tried:

Q(name__icontains=text) | Q(surname__icontains=text)

But this doesn't work, for example if I search "John Doe" it returns both of them.

Edit: Basically what I want is something like "joining" both name and surname attributes to search in, so when I search "John" it shows me "John Doe" and "John Smith", and when i search "John Doe" it shows me only the "John Doe" profile.

I think what you want is an `&` instead of `|` – dcg Mar 27 '19 at 22:56 — dcg, Mar 27 '19 at 22:56

score 3 · Answer 1 · answered Mar 28 '19 at 03:08

Try this,

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

text = "John Doe"
Profile.objects.annotate(full_name=Concat('name', V(' '), 'surname')).filter(full_name__icontains=text)

Reference

Django shell output

In [14]: from django.db.models import Value as V                                                                                                                                                                   

In [15]: from django.db.models.functions import Concat                                                                                                                                                             

In [16]: text = "John Doe"                                                                                                                                                                                         

In [17]: Profile.objects.annotate(full_name=Concat('name', V(' '), 'surname')).filter(full_name__icontains=text)                                                                                                   
Out[17]: <QuerySet [<Profile: John Doe>]>

In [18]: text = "john"                                                                                                                                                                                             

In [19]: Profile.objects.annotate(full_name=Concat('name', V(' '), 'surname')).filter(full_name__icontains=text)                                                                                                   
Out[19]: <QuerySet [<Profile: John Doe>, <Profile: John Smith>]>

In [20]: text="smith"                                                                                                                                                                                              

In [21]: Profile.objects.annotate(full_name=Concat('name', V(' '), 'surname')).filter(full_name__icontains=text)                                                                                                   
Out[21]: <QuerySet [<Profile: John Smith>]>

Johan · Answer 2 · 2019-03-28T00:01:38.870

1

This is a modified copy/paste from this answer.

I tried to come up with various ways, but they all required counting rows after each query which would make it really time consuming. Instead, the best way seem to be to split up the text by spaces and apply filters to it:

def get_profiles(text):
    qs = Profile.objects.all()
    for term in text.split():
        qs = qs.filter( Q(name__icontains = term) | Q(surname__icontains = term))
    return qs

edited Mar 28 '19 at 00:01

answered Mar 27 '19 at 23:01

Johan

3,577
1
14
28

Yes, this logic will filter on both name and surname. Do you want the second parameter (name or lastname) to be excluded from the search if it's omitted, but use both parameters if both are given? I think you need to expand your examples of what you provide and what you expect in your original question to make it a bit more clearer. – Johan Mar 27 '19 at 23:13
Yes, sorry, I will edit to add more examples. Basically what I want is something like "join" both name and surname attributes, so when I search "John" it shows me "John Doe" and "John Smith", and when i search "John Doe" it shows me only the "John Doe" profile. – MakeItFun Mar 27 '19 at 23:22
1

@MakeItFun I understand, I have updated my answer now. It's now instead a function that applies the filters which you can try – Johan Mar 28 '19 at 00:02
It works! Thank you. So 'qs' only store distinct profiles for every iteration? I don't get how it really works – MakeItFun Mar 28 '19 at 10:40
Basically the function splits the name into parts based on spaces and then filters for each part. So if we enter **John Doe**, on the first iteration it will filter out everyone with **John** in their first or last name. On the next iteration it will filter on the first result, removing everyone who doesn't have **Doe** in the first or last name. – Johan Mar 28 '19 at 13:13

Searching name and surname in Django

2 Answers2

Reference