Django Q filter, cant get results in a single query

Question

I want to make a query with multiple conditions to get some objects out of my Order model, but i can't find a way to get all the results in a single query. Besides the option to make 2 queries, i would like to know if this is possible with just one so i can create a CSV sheet with all of these orders(this part already works so i will stick to the query). The conditions:

paymethod: Paypal and Mollie
created_at: in hours 15:00 and 16:00

&

paymethod: ApplePay
created_at: in hours 17:00 and 18:00

The two queries:

Order.objects.all() \
.filter(Q(paymethod="Paypal") | 
        Q(paymethod="Mollie") & 
        Q(created_at__hour__in=(15, 16)))


Order.objects.all() \
.filter(Q(paymethod="ApplePay") 
        Q(created_at__hour__in=(17, 18)))

These two queries work fine seperatly, but i would like to know if it is possible to combine them to a single query.

i have tried something like:

Order.objects.all() \
.filter(Q(paymethod="Paypal" | "Mollie", created_at__hour__in=(15, 16)) \
& Q(paymethod="ApplePay", created_at__hour__in=(17, 18)))

The above does not work because of: TypeError: unsupported operand type(s) for |: 'str' and 'str'. So instead of paymethod="Paypal" | "Mollie" i tried: paymethod="Paypal" | paymethod="Mollie" But unfortunately that doesn't work too..

Would really appreciate if someone can point me into the right direction. I'm still learning django and django Q is new for me. If any additional information is needed please let me know! thanks!

Answer 1

I don't think you need to use Q at all. This use case should be covered by QuerySet.filter and Queryset.union.

Order.objects.filter(
    paymethod__in=["Paypal", "Mollie"], 
    created_at__hour__in=[15, 16],
).union(
    Order.objects.filter(
        paymethod="ApplePay", 
        created_at__hour__in=[17, 18],
    )
)

When you pass multiple arguments to filter, django will use sql AND in the resulting sql statement. This is equivalent to joining your Q objects with the set intersection operator &.

The QuerySet.union method is equivalent to the | operator on querysets and will translate to sql UNION. (The | operator on Q objects will translate to sql OR operations.)

Django's queryset methods that accept param__in=[...] will translate those arguments to sql IN statements. This should be equivalent to (give the same result as) using Q | Q.

filter(paymethod__in=["Paypal", "Mollie"])
# produces same results as 
filter(Q(paymethod="Paypal") | Q(paymethod="Mollie"))

I suspect IN is faster in typical cases, but you would have benchmark to confirm this.