Building Complex Dynamic Queries With Django

Question

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.

Neeraj · Accepted Answer · 2021-04-18 18:46:24Z

1

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)

answered Apr 18, 2021 at 18:46

Neeraj

9976 silver badges10 bronze badges

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

Comments

Iain Shelvington · Accepted Answer · 2021-04-18 19:07:11Z

1

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

edited Apr 18, 2021 at 19:07

answered Apr 18, 2021 at 18:47

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Comments

fed_tf · Accepted Answer · 2021-04-18 19:05:14Z

0

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.

answered Apr 18, 2021 at 19:05

fed_tf

1261 silver badge8 bronze badges

Collectives™ on Stack Overflow

Building Complex Dynamic Queries With Django

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related