How to use "AND" in a Django filter?

Question

How do I create an "AND" filter to retrieve objects in Django? e.g I would like to retrieve a row which has a combination of two words in a single field.

For example the following SQL query does exactly that when I run it on mysql database:

SELECT * FROM myapp_question
WHERE ((question LIKE '%software%') AND (question LIKE '%java%'))

How do you accomplish this in Django using filters?

score 168 · Answer 1 · edited Feb 28 '21 at 16:53

168

For thoroughness sake, let's just mention the Q object method:

from django.db.models import Q
criterion1 = Q(question__contains="software")
criterion2 = Q(question__contains="java")
q = Question.objects.filter(criterion1 & criterion2)

Note the other answers here are simpler and better adapted for your use case, but if anyone with a similar but slightly more complex problem (such as needing "not" or "or") sees this, it's good to have the reference right here.

edited Feb 28 '21 at 16:53

Amin Mir

640
8
15

answered Apr 20 '09 at 21:04

David Berger

12,385
6
38
51

6

Just wanted to mention your passing comment helped me. Thanks! – Alfonsol Mar 01 '16 at 17:20
See here for more info: https://docs.djangoproject.com/en/3.1/topics/db/queries/#complex-lookups-with-q – theQuestionMan Oct 14 '20 at 01:09

score 113 · Accepted Answer · edited Jun 14 '19 at 07:03

113

(update: this answer will not work anymore and give the syntax error keyword argument repeated)

mymodel.objects.filter(first_name__icontains="Foo", first_name__icontains="Bar")

update: Long time since I wrote this answer and done some django, but I am sure to this days the best approach is to use the Q object method like David Berger shows here: How do I use AND in a Django filter?

edited Jun 14 '19 at 07:03

Anupam

14,950
19
67
94

answered Apr 20 '09 at 19:57

Andrea Di Persio

3,246
2
24
23

5

This does _not_ work for me in Django 1.6 and Postgres. I get a "SyntaxError: keyword argument repeated" whenever there are two or more the same keywords. Only the solution with Q by David Berger works. – margusholland May 30 '14 at 07:32
@margusholland The answer works for me with Django 1.6 & Postgres. Can you post your query. It's as official as Q object method. https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships – user Jul 08 '14 at 19:15
1

I have a model Country with a short country code field (cc_short): >>> countries = Country.objects.filter(cc_short__icontains='A', cc_short__icontains='B') File "", line 1 SyntaxError: keyword argument repeated – margusholland Jul 09 '14 at 08:56
1

This also _does not_ work for me either. Same error as @margusholland. Using Django 1.7.5. Code in question: `talks = Talks.objects.filter(description__contains=topics,description__contains=terms).order_by('-datetime')` Topics and terms are both lists. – Austin A Apr 21 '15 at 20:37
I am also getting the SyntaxError: keyword argument repeated coming if I fire the similar query. My query `media_house_writers = ScatterxBloggerProfile.objects.filter(level__icontains="media_house", level__icontains="publisher")`. Plz help. – Namita Maharanwar Jan 05 '16 at 10:32
7

Note that both fields are the same! It's python itself that doesn't allow this, it won't work in ANY django/db. This should not be an accepted answer. – rsalmei Jan 17 '17 at 16:25
Long time since I wrote this answer and done some django, but I am sure to this days the best approach is to use the Q object method like David Berger shows here: http://stackoverflow.com/a/770078/63097. Also https://docs.djangoproject.com/en/1.11/topics/db/queries/#complex-lookups-with-q-objects – Andrea Di Persio May 11 '17 at 14:44
@gath the accepted answer needs to be changed since this one would not work – Anupam Jun 14 '19 at 07:03
@rsalmei will it work if both filter fields are different – mayankmehtani May 18 '20 at 13:34
1

Hey @MayankMehtani, it should work if the keyword arguments themselves are different, not only their values. – rsalmei May 18 '20 at 22:58
It is working for different fields only not the same field. – Kamar Apr 25 '21 at 08:21

score 17 · Answer 3 · edited Nov 27 '17 at 23:42

17

You can chain filter expressions in Django:

q = Question.objects.filter(question__contains='software').filter(question__contains='java')

You can find more info in the Django docs at "Chaining Filters".

edited Nov 27 '17 at 23:42

ppython

485
6
19

answered Apr 20 '09 at 20:01

mipadi

398,885
90
523
479

14

This answer may give you wrong results depending on your scenario. Using `,` in filter vs chaining filters can have different results. Further reading : [SO Answer](http://stackoverflow.com/a/11025652/781695) & [Official docs](https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships) – user Jul 08 '14 at 19:16
1

this is NOT answer for the asked question. It will give diff output. But this is what I was looking for. What OP wanted, allobjects.filter(name=x or name= y) but this answer will give first = allobjects.filter(name=x) then filter first.filter(name=y). Hope you guys get it. Sorry for pseudo code. – mohammed_ayaz Apr 18 '20 at 18:44
3

@mohammed_ayaz The question asks for an AND operation, not an OR operation. – mipadi Apr 19 '20 at 20:10
I am really curious why this answered was even upvoted. Besides the fact that this has an OR logic, it will have different results if the filtered fields are from referenced tables. – Alex M.M. May 06 '20 at 09:24
1

@Alexandru-MihaiManolescu: What has OR logic? This question is an AND, not an OR. – mipadi May 07 '20 at 16:50
1

In `OneToMany` and `M2M` relations it should be used with caution, as it applies to any object linked to the primary model, not necessarily those objects that were selected by an earlier `filter()` call. – Amin Mir Jun 17 '20 at 07:04

score 1 · Answer 4 · answered Dec 22 '22 at 14:51

You can use AND with filter() using & or Q() and & as shown below:

# "store/views.py"

from .models import Question
from django.db.models import Q
from django.http import HttpResponse

def test(request):

    # With "&"
                                                              # ↓ Here
    qs = Question.objects.filter(question__contains="software") & \ 
         Question.objects.filter(question__contains="java")
    print(qs)

    # With "Q()" and "&"
                               # ↓ Here                         # ↓ Here
    qs = Question.objects.filter(Q(question__contains="software") & 
                                 Q(question__contains="java"))
    print(qs)                  # ↑ Here

    return HttpResponse("Test")

How to use "AND" in a Django filter?

4 Answers4

Linked