CODEWITHSUNDEEP
pythondjango
iampre409
iampre409

Reputation: 357

Building Complex Dynamic Queries With Django

Here is a sample model I have:

class Commission(models.Model):
  status                     = models.CharField(null=True, blank=True)
  customer_name              = models.CharField(null=True, blank=True)
  order_dat                  = models.CharField(null=True, blank=True)

For status, I can have up to 10 different status types, such as: requested, confirmed, paid...

If I want to build a way to dynamically filter all orders with a specific status with a specific customer, I would do the following:

sample_kwargs['status'] = 'active'
sample_kwargs['custom_name'] = 'John Doe'

Then get my results with the following:

all_commmissions = Commission.objects.filter(**sample_kwargs)

This all works well, but what I am now trying to do is is get all the customer's orders that are equal to multiple statuses. For example, I want all of John Doe's orders that are equal to both confirmed, and paid.

Normally I'd used the Q filter

  all_commmissions = Commission.objects.filter(
                     Q(status='confirmed') |
                     Q(status='paid'))

But this only allows me to hard code things in. How can I build a complex dynamic query (such as my sample_kwargs example) that uses OR? Or what is the best way I can achieve this? I could not find anything in the documentation. Thank you for your help.

Upvotes: 0

Views: 94

Answers (3)

Iain Shelvington
Iain Shelvington

Reputation: 32244

You can use the __in filter to return results that have a status in any of the values. setdefault can be used to create and append to a list of values dynamically

 sample_kwargs.setdefault('status__in', []).append('confirmed')
 sample_kwargs.setdefault('status__in', []).append('paid')

Upvotes: 1

fed_tf
fed_tf

Reputation: 126

When you write

Commission.objects.filter(status='active', custom_name='John Doe')

this is basically a shortcut for

Commission.objects.filter(Q(status='active') & Q(custom_name='John Doe'))

Now, if you want to get Comissions with status either "paid" or "confirmed" and with custom_name "John Doe" via Q objects, you can do

Commission.objects.filter(
    (Q(status='active') | Q(status='confirmed')) & Q(custom_name='John Doe'))

However, as Neeraj showed, in your particular case you don't need to use Q objects and can simply use '__in' lookup for multiple statuses.

Upvotes: 0

Neeraj
Neeraj

Reputation: 997

You can simply change your status filter params to a list:

sample_kwargs['status__in'] = ['confirmed', 'paid']
sample_kwargs['custom_name'] = 'John Doe'
all_commmissions = Commission.objects.filter(**sample_kwargs)

Upvotes: 1

Related Questions