A Django ORM query using a mix of filter() and Q objects

Question

I'm looking to create a slightly more complex query that is written fairly easily using raw SQL. Here's an example of the query in raw:

SELECT my,fields FROM sales WHERE is_paid = False OR status = 'toship' AND otherfield = 'FOO' AND anotherfield = 'BAR'

This is simple, it generates all the results that are is_paid = False and then a second result set for my AND matches.

Now I know about Q objects, I know about filtering but I can't seem to wrap my mind around how to achieve this in the Django ORM cleanly.

Any tips?

Thanks

tarequeh · Accepted Answer · 2009-07-23 18:19:10Z

26

You can keep building your Q object in a somewhat dynamic fashion.

Example:

query1 = Q(is_paid=False)

query2 = Q()

if status:
    query2 = Q(status=status)

if otherfield:
    query2 = query2 & Q(otherfield=otherfield)

if anotherfield:
    query2 = query2 & Q(anotherfield=anotherfield)

query = query1 | query2

result = model.objects.filter(query)

answered Jul 23, 2009 at 18:19

tarequeh

1,81918 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Roseman · Accepted Answer · 2009-07-22 18:27:27Z

15

Although googletorp is right that you can't construct the query dynamically with a string, you can do it with dictionary parameters. Something like:

model.objects.filter(Q(**mydict1) | Q(**mydict2))

where mydict1 and 2 are of the form:

{'field1': 'value1'}
{'field2__icontains': 'value2'}

etc.

answered Jul 22, 2009 at 18:27

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Comments

elad silver · Accepted Answer · 2017-01-13 17:28:25Z

3

This is a great way to do dynamic "OR" querying:

import operator
from django.db.models import Q
from your_app.models import your_model_object

q_list = [Q(question__startswith='Who'), Q(question__startswith='What')]
your_model_object.objects.filter(reduce(operator.or_, q_list))

if you want to use "AND":

your_model_object.objects.filter(reduce(operator.and_, q_list))

answered Jan 13, 2017 at 17:28

elad silver

9,7816 gold badges47 silver badges71 bronze badges

Comments

googletorp · Accepted Answer · 2009-07-22 16:18:54Z

2

Something like this should work:

model.objects.filter(Q(is_paid=False) | Q(status='toship', otherfield='FOO', anotherfield='BAR'))

Edit: You can't create the query dynamically the same way you can construct a string containing a SQL statement to be executed when completed. If you want to do this, I would suggest using an if state, function or what suits your use case best:

if query == 'simple':
    result = model.objects.filter(Q(is_paid=False))
else:
    result = model.objects.filter(Q(is_paid=False) | Q(status='toship', otherfield='FOO', anotherfield='BAR'))
for items in result:
    ...

This could be more complex, but I'm sure you get the idea.

edited Jul 22, 2009 at 16:18

answered Jul 22, 2009 at 15:42

googletorp

33.3k15 gold badges69 silver badges82 bronze badges

2 Comments

Bartek Over a year ago

Ok I guess my issue is now. Let's say I have a query that is essentially this: model.objects.filter(Q(is_paid=False)) And I want to add another Q object to that query with some dynamic values to result in essentially the same query you posted, but it'd be dynamically created. That's where I am struggling on in this case. How would I achieve that?

elad silver Over a year ago

Don't forget from django.db.models import Q

Collectives™ on Stack Overflow

A Django ORM query using a mix of filter() and Q objects

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related