CODEWITHSUNDEEP
djangodjango-queryset
Alf
Alf

Reputation: 852

How do you search over multiple fields in a Django model

PsuedoCode for Model called Person:

id         =  primary key
first_name =  charField...
last_name  =  charField...

My Query:

queryset_list = Person.objects.filter(Q(id__icontains=id_filter) & (Q(first_name__icontains=name_filter) | Q(last_name__icontains=name_filter)))

id_filter and name_filter come from a form. If name_filter is "Betty", then the person named Betty Bee shows up. However, my problem is if name_filter is "Betty B", nothing will show up (understandably). How would I do a search similar in effect to:

Q( (first_name + last_name)__icontains=name_filter )?

EDIT- It seems like more detail would have helped as I slightly simplified the problem. I'm using a SQLite DB locally and a MySQL DB on production. Also, I'm actually working with a Student model that is separate from the person model.

Based on the best answer so far:
students.filter(Q(id__icontains=id_filter)).extra(
                where=['lower(concat(base_Base_person.first_name, " ", base_Base_person.last_name)) like %s'],
                params=['%%%s%%' % name_filter]
            )

The query works! Yay!

Upvotes: 2

Views: 1458

Answers (1)

ndpu
ndpu

Reputation: 22571

You can do this with extra.

Query for postgres:

# || is string concatenation in postgres
# query = "Betty B"
queryset_list = Person.objects.extra(
    where=['first_name || ' ' || last_name ilike %s']
    params=['%%%s%%' % query]
)

Result query will be similar to:

SELECT * FROM appname_person WHERE first_name ||' '|| last_name ilike 'Betty B';

For MySql this query should work:

# concat using for string concatenation in Mysql;
queryset_list = Person.objects.extra(
    where=['lower(concat(first_name," ",last_name)) like %s']
    params=['%%%s%%' % query]
)

Upvotes: 2

Related Questions