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

Question

I want to create a slightly more complex query that is easily written using raw SQL. Here is an example request in raw:

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

It's simple, it generates all results that are is_paid = False, and then a second set of results for AND matches.

Now I know about Q objects, I know about filtering, but I can’t imagine how to do this cleanly in Django ORM.

Any tips?

thanks

Answer 1

You can continue to build your Q object somewhat dynamically.

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) 

Answer 2

Although googletorp is right that you cannot dynamically build a query with a string, you can do this with dictionary parameters. Sort of:

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

where mydict1 and 2 are of the form:

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

and etc.

Answer 3

Something like this should work:

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

Edit: You cannot create a query dynamically in the same way as you can build a string containing an SQL statement that will be executed upon completion. If you want to do this, I would suggest using an if state, a function, or what works best for your use:

 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: ... 

It may be more complicated, but I'm sure you understand.

Answer 4

This is a great way to execute a dynamic OR query:

 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))