Django: Query using contains each value in a list

Question

I need to perform a django query that checks if a field contains all values within a list. The list will be of varying length

Example

User.objects.filter(first_name__contains=['x','y','z'])

score 132 · Accepted Answer · edited Apr 07 '20 at 07:20

132

import operator
from django.db.models import Q

User.objects.filter(reduce(operator.and_, (Q(first_name__contains=x) for x in ['x', 'y', 'z'])))

for python 3

from functools import reduce

.

edited Apr 07 '20 at 07:20

mmgross

3,064
1
23
32

answered Jan 28 '11 at 04:23

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

20

`User.objects.filter(reduce(...))` He is doing the equivalent of `User.objects.filter(Q(first_name__contains='x') & Q(first_name__contains='y') & Q(first_name__contains='z'))` – Yuji 'Tomita' Tomita Jan 28 '11 at 04:33
Very useful! You can also check to see if *any* of items in the list is contained in any of the objects by using operator.or_ – Neil Oct 27 '12 at 06:37
1

what is operator? Do I need to import it? – simi Feb 27 '14 at 14:57
@simi: You always need to import. – Ignacio Vazquez-Abrams Feb 27 '14 at 15:01
@IgnacioVazquez-Abrams Your code is returning two results for the same db row if the row is assigned to two tags being searched! Please help. – Adam Mar 11 '14 at 19:44
@AdamSilver: https://docs.djangoproject.com/en/dev/ref/models/querysets/#distinct – Ignacio Vazquez-Abrams Mar 11 '14 at 19:52
Is it possible to order by most relevant depending on the number matching terms in each result? – Jesse Greathouse Sep 05 '14 at 14:44
2

@IgnacioVazquez-Abrams If you wanted to use this within a for loop, is there any way to replace `first_name` with a variable that changes on each iteration (`first_name`, `last_name` etc) *while keeping* the `__contains`? Right now the only way I can see to run this against multiple fields is repeating the entire function and hardcoding the different field names. – toxefa Jan 07 '17 at 02:44
1

@py4on: http://stackoverflow.com/questions/2932648/how-do-i-use-a-string-as-a-keyword-argument – Ignacio Vazquez-Abrams Jan 07 '17 at 03:42
@IgnacioVazquez-Abrams lifesaver thanks - just seems weird appending `__contains` to a dictionary key string - never even tried it! – toxefa Jan 07 '17 at 11:12
This is an incredible solution. It also fixed my issue where I needed to find all the rows that had one value in a given list by changing `contains.and_` to `contains.or_` – Reimus Klinsman Apr 26 '17 at 00:44
2

This line of code is very precious, but lack of any explanation makes this a terrible answer. – cezar Jun 12 '18 at 11:06
For anyone else who finds themselves in @ReimusKlinsman 's situation, wanting to check if the field contains "at least one of" the values you provide in a list -- you don't need all this code. The following will suffice: `YourModelClass.objects.filter(fieldName___in=your_list)` – Will Apr 23 '20 at 10:38
1

How's the perf for large list? – Siraj Alam Mar 10 '22 at 11:23

score 38 · Answer 2 · edited Feb 13 '20 at 20:50

38

import operator
from django.db.models import Q

q = ['x', 'y', 'z']
query = reduce(operator.and_, (Q(first_name__contains = item) for item in q))
result = User.objects.filter(query)

edited Feb 13 '20 at 20:50

MohitC

4,541
2
34
55

answered Oct 28 '14 at 16:37

user2872619

381
3
4

7

Hey :). Welcome to SO. Mind adding a bit of meat to your answer? We normally like to provide a bit more info than a simple code-dump for an answer, just to help other users better understand what's happening :). Thank you ^^ – Patrice Oct 28 '14 at 16:56
20

@Patrice Might as well make the same comment to the chosen answer, by the guy with 339K rep. This answer has more context at least. :) – bozdoz Jan 19 '15 at 01:13
6

if using Python 3, use `import functools` and use `functools.reduce`. See [this](https://stackoverflow.com/a/10226421/1526703) – Anupam Jan 20 '18 at 07:45

Mark Mishyn · Answer 3 · 2022-06-14T15:25:25.510

5

More readable solution.

qs = User.objects.all()
for search_term in ('x', 'y', 'z'):
    qs = qs.filter(first_name__contains=search_term)

Note: Querysets are lazy, so this code makes 1 DB query.

edited Jun 14 '22 at 15:25

answered Mar 26 '21 at 16:44

Mark Mishyn

3,921
2
28
30

1

Yeah, this works well for `AND` (`&`) queries. For `OR` (`|`) queries, `Q` objects must be used. – xyres Mar 28 '21 at 11:52
I ended up using this same approach. I think this solution is actually much more concise and much more readable than chaining multiple `Q`s in a line. And it suits op's problem well. – Brandon Dec 10 '21 at 05:09
@Brandon yes but as @xyres said, for `OR` operator, you have to use `Q` objects – lbris Feb 10 '22 at 13:10

Michel Dudouze · Answer 4 · 2018-10-05T14:59:52.377

1

from django.db.models import Q
User.objects.filter(Q(first_name__contains=x)&Q(first_name__contains=y)&Q(first_name__contains=z))

Works for me on Django 1.8 python 2.7

doc => https://docs.djangoproject.com/en/1.8/ref/models/querysets/#q-objects

for more recent => https://docs.djangoproject.com/en/2.1/ref/models/querysets/#q-objects

edited Oct 05 '18 at 14:59

answered Oct 05 '18 at 14:54

Michel Dudouze

11
4

score 1 · Answer 5 · answered Mar 11 '20 at 14:06

The accepted solution didn't work for me, but this did:

list = ['x', 'y', 'z']
results = User.objects.filter(first_name__contains=list[0])
del list[0]

for l in list:
    results = results.filter(first_name__contains=l)

The first results variable will store a list of all the objects with the first_name name field value 'x'.

And then in the for loop you filter for 'y' amongst the first filter results. Now you have a QuerySet that should contain a list of items where both 'x' and 'y' can be found. Now amongst those you filter for any item that contains 'z' as well.

This should work for any length list.

This works fine. You can even lose del statement, by simply slicing the list, i.e., `for l in list[1:]:` — AmateurAardvark, Mar 06 '23 at 20:10

score 0 · Answer 6 · answered Sep 21 '21 at 17:51

It doesn't apply exactly here, but, if you have a ManyToManyField and you want to check if it contains a specific value, check this https://www.revsys.com/tidbits/tips-using-djangos-manytomanyfield/.

I have a "Products" and Django native "Users" model. My "Products" model has a many-to-many field users pointing to "Users". Then, I wanted to check if this list-like field contained the logged in user. I did that by ...users__username_icontains=request.user.username... and the link above helped me to understand better what is a many-to-many field and how it works.

score 0 · Answer 7 · edited Sep 28 '21 at 13:28

0

the_list= []

data_list= ['x', 'y', 'z'] 

for i in data_list:

    a = User.objects.filter(first_name__contains=project).values('etc')

    the_list+= a

edited Sep 28 '21 at 13:28

bad_coder

11,289
20
44
72

answered Sep 28 '21 at 12:42

Rodut Naitsirc

21
2

4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 28 '21 at 13:28

score -1 · Answer 8 · answered Mar 17 '21 at 10:56

This worked for me in django 2.2, python 3.8, using lambda instead of 'operator'. Explanations on lambda can be found here: https://www.python-course.eu/lambda.php

from functools import reduce
from django.db.models import Q

my_list = ['x','y','z']
User.objects.filter(reduce(lambda x, y: x & y, [Q(first_name__contains= i for i in my_list]))

Django: Query using contains each value in a list

8 Answers8

Linked