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.