How to chain unknown number of Q expressions with OR relationship?

Using Django Using the ORM
1

Hello there,

I have the following situation:
I have a (yet unresolved) queryset of Product which I suppose I need to filter by chaining an unknown number of Q expressions in an OR relationship.

Basically, I would need to do this:

incoming_filters = {
    product_type_id1: [product_property_id1, ...],
    product_type_id2: [product_property_id2, ...],
    product_type_id3: [product_property_id3, ...],
    ...
}
product_queryset.filter(
    Q(product_type=product_type_id1, product_properties__in=[product_property_id1, ...])
    |
    Q(product_type=product_type_id2, product_properties__in=[product_property_id2, ...])
    |
    Q(product_type=product_type_id3, product_properties__in=[product_property_id3, ...])
    |
    ...
)

How could I do this?

Some extra info:
To make matters worse, there is actually an intermediary between Product on the one side, and ProductType and ProductProperty on the other side:
Product <=OneToOne== ProductTagging ==ForeignKey=> ProductType
Product <=OneToOne== ProductTagging <=ManyToMany== ProductProperty
I don’t initiate the queryset, I’m getting it from “the messy part” of the code, which I’d rather not modify. It’s the reason why I can’t start from “ProductTagging” and save myself some trouble.

2

See the responses at Store Q()-filter terms in Database? - #2 by KenWhitesell and Reusable Queries with Q - #2 by KenWhitesell for the key concepts here.

As long as you can obtain the proper data from those incoming filters, you can build the queries you need.

However, be aware that dynamically constructing a query from submitted data is the very definition of a SQL injection vulnerability, and that you must never directly trust anything being submitted by a browser without strong validation. (Read the complete thread at the first link to get an idea of one of the potential issues.)

3

Hi Ken, as always thanks for you super quick reply.

So I get how I could unpack an unknown number of dict-like objects into Q expressions. But they would be in an AND relationship. I want them to be in an OR relationship between themselves, however. I can’t see how to do that.

Good point about the SQL injection. I simplified the case a bit, but actually the incoming product_type_ids and product_property_ids are validated at this point.

4

The boolean OR operator | works with two Q objects, returning a Q object. You can then use that same operator with a third, fourth, fifth, etc Q object.

This means that if you have a list of conditions you want to apply as an aggregate OR, you can use the functools.reduce method to apply them across the list.

As a trivial example, assume I want to filter on the primary key of my User model. I have, somehow, created this list:

q_list = [Q(id=1), Q(id=3), Q(id=5), Q(id=7)]

(Note that each of those Q objects could have been created dynamically in forms such as Q(**{id, 1}), etc)

I can then combine these as:

composite_q = functools.reduce(Q.__or__, q_list)

I can then use that in my query:

qset = User.objects.filter(composite_q)

If I then print out the query:

print(qset.query)

I get something like:

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" 
FROM "auth_user" 
WHERE ("auth_user"."id" = 1 
  OR "auth_user"."id" = 3 
  OR "auth_user"."id" = 5 
  OR "auth_user"."id" = 7)
1 Like
5

Just reduce it off. I don’t know why I didn’t think about that! Thanks a lot.