Opened 11 years ago
Last modified 8 months ago
#22158 new New feature
Allow model level custom lookups
Reported by: | Anssi Kääriäinen | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette, Ülgen Sarıkavak | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
The idea is to allow DRY of common queries. For example, lets consider case where we have employee model with start and end dates. We want to filter all active employees. The basic query looks something like this:
Employee.objects.filter(start__lte=today(), Q(end__gte=today()) | (end__isnull=True))
it is possible to implement this as manager (say .active()) method, but that doesn't allow usage of the definition in related queries (that is, it is not possible to do Task.objects.filter(employee__active=True)
. Being able to do that would be a big win in DRYing up query generation.
The idea is to allow for class based lookups. I see two ways forward:
- Allow returning of Q() objects from Employee.get_model_lookup(lookup_name, value). Employee.get_model_lookup('active', True) would return
Q(start__lte=today(), Q(end__gte=today()) | (end__isnull=True))
. - Allow returning SQL expressions from get_model_lookup(). The example case would be handled by directly constructing the needed SQL for the active query.
For related querying (that is, employee__active=True
) the Q() object returned from get_model_lookup() must be rewritten to Q(employee__start__lte=today(), Q(employee__end__gte=today()) | (employee__end__isnull=True))
. This should be doable inside the ORM.
For the example case the Q() way would be a lot easier to use. But there likely exists cases where one wants to use SQL directly, so allowing for returning Q-objects or SQL expressions seems like a good goal.
It might be handy to allow for registration of model lookups. This could allow for fields to register their own model level lookups, or for 3rd party apps to register lookups for all models.
Change History (6)
comment:1 by , 11 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 10 years ago
This would be incredibly helpful - I'm currently having to bodge together my own solution for making custom lookups that can refer to >1 field.
comment:3 by , 9 years ago
I came up with another instance where this feature would be useful for one of my projects, and decided it might be time to move forward with this. The syntax I came up with was:
class User(models.Model): valid_from = models.DateField() valid_until = models.DateField(null=True, blank=True) def is_active_at(self, dt): return (self.valid_from < dt and (self.valid_until is None or self.valid_until > dt)) @models.lookup def is_active_at(cls, dt): return (Q(valid_from__lt=dt) & (Q(valid_until__gt=dt) | Q(valid_until__isnull=True))) @property def valid_from_year(self): return self.valid_from.year @models.lookup def valid_from_year(cls): return Extract(F('valid_from'), 'year')
So, the idea is that you could do both:
User.objects.filter(is_valid_at=now())
and
user.is_valid_at(now())
Similarly you could do both:
User.objects.filter(valid_from_year__lte=2000)
and
user.valid_from_year <= 2000
The idea would be that we use some decorators to mark methods for database lookups and expressions. When the transform/expression is referenced by the ORM, we first call the decorated method, then we resolve the expression. It might be worth it to pass the query to the methods, too.
Does this seem like an OK API?
comment:4 by , 9 years ago
Cc: | added |
---|
I like your proposed API, it handles all the cases I can think of.
It also solves a limitation of custom managers used to filter their initial queryset, they can't be combined.
class Person(models.Model): ROLE_AUTHOR = 'A' ROLE_EDITOR = 'E' ROLE_CHOICES = [ (ROLE_AUTHOR, _('Author')), (ROLE_EDITOR, _('Editor')), ] dob = models.DateField() role = models.CharField(max_length=1, choices=ROLE_CHOICES) @models.lookup def author(cls): return Q(role=self.ROLE_AUTHOR) @models.lookup def born_90s(cls): return Q(dob__year__gte=1990, dob__year__lt=2000) Person.objects.filter(author=True, born_90s=True)
Would you mind exposing this approach to the @developpers mailing list? I think we could get interesting feedback there.
comment:5 by , 2 years ago
With the approach we've taken with RegisterLookupMixin
this could be implemented by
- Having
models.Model
extendRegisterLookupMixin
- Adding a
lookup
decorator as described above that wraps the decorated function in something that has acontribute_to_class
method. This method would then callmodel.register_lookup
with a lookup factory. - Adjusting
sql.Query.build_lookup
to considerself.model.get_lookup
and call into the wrapped function withrhs
e.g. the object that could be registered as a model lookup would be something like
class ModelLookupFactory: def __init__(self, method): self.model = None self.method = method def contribute_to_class(self, cls, name, private_only=False): self.model = cls cls.register_lookup(self, name) def __call__(self, rhs): # XXX: Use introspection on self.method to avoid passing rhs # if the arity of self.method is 1. Ensure isinstance(rhs, bool) # and negate if necessary return lookups.Exact(self.method(self.model, rhs), True) lookup = ModelLookupFactory
comment:6 by , 8 months ago
Cc: | added |
---|
It should definitely be able to handle Q objects as well as SQL as this is the 90% use case I think. Really nice feature idea though!