Everyone knows about Django's Q objects and how to chain them together for complex lookups. The syntax is straight forward.

results = MyModel.objects.filter(Q(key1="value1") | Q(key2="value2"))

That generates this unexciting SQL:

SELECT *
FROM `my_table`
WHERE (`my_table`.`key1` = "value1" OR `my_table`.`key2` = "value2")

But what happens when you have an unknown amount of filters? There's no obvious way to chain together those statements in a dynamic way. This puzzle had escaped me for a while, but this morning I finally figured it out and wrapped the logic into the following QuerySet function:

# querysets.py
import operator
from django.db.models.query import QuerySet
from django.db.models import Q

class MyQuerySet(QuerySet):
    def dynamic_or(self, **kwargs):
        or_statements = []
        for key, value in kwargs.items():
            or_statements.append(Q(**{key:value}))

        return self.filter(reduce(operator.or_, or_statements))

# models.py
from django.db import models
from querysets import MyQuerySet

class MyModel(models.Model):
    # usual fields stuff
    
    objects = MyQuerySet.as_manager()

This means you can assemble dynamic dictionary of filters and run them like so:

filters = {
    "key1": "value1",
    "key2": "value2",
     ...
}

matches = MyModel.objects.dynamic_or(**filters)

This, very excitingly, generates the following SQL:

SELECT *
FROM `my_table`
WHERE (
    `my_table`.`key1` = "value1" OR
    `my_table`.`key2` = "value2" OR
    ...
)

It's not every day that you need this functionality, but if you do this can help you stay out of raw SQL land. And what a dark, dark land that is.


comments powered by Disqus