Django ANDing queryset filters using variable assignment

Question

Let's say I have a queryset of Products that has tons of prefetch objects in it. Among the prefetch objects are "categories", and categories can have "filters". Not every Product has categories and filters defined.

Example categories could be "price" and "color", and filters could be "lt $5" and "blue".

I can get only the Products that have "price" and "color" defined easily enough, using exclude.

But how do I get this, for example:

All the Products that have "price"=* defined, and all the Products that have "color"="blue" defined.

Any ideas?

EDIT: I'm gonna go ahead and show the solution I came up with. Bear in mind that the example above is very much simplified. There are dozens of categories, each with dozens of filters.

Imagine that the the user chose category 'shoes' but not filters 'shoes': 'women' or 'shoes': 'football'.

And she chose category 'price' AND filter 'price': 'lt $5'

from django.db.models import Q
import operator
argument_list = []
# categories_selected = all the categories that have been checked on the page     
for c in categories_selected: 
# categories_filtered = all the (selected) categories that have filters applied
    if c not in categories_filtered:
        # Here we get all where <category>_id is defined
        argument_list.append(Q(**{c+'__id__isnull': False}))
    else:
        for f in get_category_filters_from_url(filters_selected, c):
        # get_category_filters_from_url() returns all the filters (by id) belonging 
        # to a given category, e.g., 'price': 'lt $5'
            argument_list.append(Q(**{c+'__id': f}))

resources = resources.filter(reduce(operator.or_, argument_list)).distinct()

I hope that's clear enough, and that it helps someone else muddle through something like this.

Looks like you need not the AND, but OR condition here, right? Your example can be rephrased to: "All the Products with price = A or color = B". — Ihor Pomaranskyy
– Ihor Pomaranskyy, Commented Jun 10, 2015 at 14:52

Caumons · Accepted Answer · 2015-06-10 15:06:48Z

3

Without knowing how your models look like, I'd use the following query:

Query with AND:

Product.objects.filter(categories__price__isnull=False, categories__color='blue')

Query with OR:

from django.db.models import Q

Product.objects.filter(Q(categories__price__isnull=False) | Q(categories__color='blue'))

edited Jun 10, 2015 at 15:06

answered Jun 10, 2015 at 14:58

Caumons

9,64514 gold badges71 silver badges85 bronze badges

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

4 Comments

Rob L Over a year ago

I like it. Correct me if I'm wrong, but this technique is the equivalent of sql AND, where filter chaining is the equivalent of OR. Is that right?

Caumons Over a year ago

When you separate directly with commas you're using AND. If you need OR, then you should use Q objects. See: docs.djangoproject.com/en/1.8/topics/db/queries/…

Rob L Over a year ago

Yes, but I have an undetermined number of filters. Is there a way to build the Q object statement using **kwargs?

Caumons Over a year ago

Yes, you can use **kwargs in both cases. I've updated my response with an example of Q objects.

Collectives™ on Stack Overflow

Django ANDing queryset filters using variable assignment

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related