3

I have a model I want to filter with a search term, which is usually a name. However in my database first_name and last_name are two separate fields.

e.g.

search_term = 'Will Sm'

db_persons record: first_name = 'Will', last_name = 'Smith'

the search term would be able to retrieve this record.

How can I achieve that?

db_persons.objects.filter(__?__)

UPDATE:

Looking for a way to concatenate fields and querying the concatenated result without raw sql

zentenk
  • 2,725
  • 6
  • 25
  • 31
  • possible duplicate of [Querying full name in Django](http://stackoverflow.com/questions/7681708/querying-full-name-in-django) – laffuste Feb 05 '14 at 03:54

2 Answers2

6

If you want to search in first AND lastname you can simply use:

db_persons.objects.filter(first_name='Will', last_name='Smith')

Use Q objects if you want to search in first_name OR last_name:

from django.db.models.query_utils import Q
db_persons.objects.filter(Q(first_name='will') | Q(last_name='will'))

Update according comment: A simple solution could look like this:

search_string = 'will smith'
query_list = search_string.split()
db_persons.objects.filter(Q(first_name__in=query_list) | Q(last_name__in=query_list))

If you use mysql, you could use search.

Reto Aebersold
  • 16,306
  • 5
  • 55
  • 74
  • but what if the user searches `'will smith'`? – zentenk Sep 26 '11 at 15:50
  • hmm not quite what I am looking for, perhaps because I need to have chinese search terms as well which often has no spaces and also have first and last names. I was more looking for a way to concatenate the first and last names into aliases and run a subquery on those aliases for a match while avoiding raw sql. – zentenk Sep 26 '11 at 16:30
2

A simple solution will be to add another field complete_name on your model. On save you will update this field by concatenate first_name and last_name fields without any space (you need to strip all spaces from the concatenation result). Then you will do your query on this field with the search_term but with spaces also stripped.

Simple example to give you the general idea:

class Person(models.Model):
    first_name = CharField(...)
    last_name = CharField(...)
    complete_name = CharField(...)

    def save(self, *args, **kwargs):
        complete_name = '%s%s' % (self.first_name, self.last_name)
        self.complete_name = complete_name.replace(' ', '')
        super(Person, self).save(*args, **kwargs)

results = Person.objects.filter(complete_name__icontains=search_term.replace(' ', ''))
Etienne
  • 12,440
  • 5
  • 44
  • 50
  • can't believe I didn't think of that... now i wont need a subquery, thanks! – zentenk Sep 26 '11 at 17:21
  • Also you can elaborate on what you put in `complete_name`. It depends of your use case but you could put `first_namelast_name last_namefirst_name` (ex. 'WillSmith SmithWill'). You could also strip accents and special characters in both your field and the search term. Sometimes, with this kind of tricks, you can get better results. But it all depends of your use cases and data. I don't have any experience with chinese. – Etienne Sep 26 '11 at 18:32