CODEWITHSUNDEEP
sqldjango
Boris Burkov
Boris Burkov

Reputation: 14456

Django: how to add a custom attribute to each element of queryset without N+1 request?

Suppose that I have a very basic user model with name and age:

class User(models.Model):
    name = CharField()  # e.g. John Smith
    age = IntegerField()  # e.g. 21

I want to filter users 18+ y/o and for each of them add a special attribute namesakes (for instance, for "John Smith" it would be something like ["John Williams", "John for Neumann", ...]).

I know, how to do this in N+1 requests:

for user in User.objects.filter(age__gt=18):
    user.namesakes = User.objects.filter(name__startswith=user.name.split()).\
                                   all()

But how do I do this in one-ish request? Ideally, for each User object in queryset I'd like to create a custom attribute namesakes that contained a queryset of namesakes. This is very similar to what annotate() or prefetch_related() with to_attr do, but not exactly.

I'd also prefer to avoid using raw SQL, if possible.

Thank you.

Upvotes: 0

Views: 786

Answers (1)

Boris Burkov
Boris Burkov

Reputation: 14456

I found a blog post, doing something very similar to what I need and.... dear god...

>>> from django.db.models import F, Q, Case, IntegerField, Sum, Value, When
>>> from django.db.models.functions import Coalesce
>>> pizzas = Pizza.objects.annotate(
...     uses_name_of_another_pizza_as_name_prefix=Coalesce(
...         Sum(
...             Case(
...                 When(
...                     Q(
...                         ~Q(pk=F('category__pizzas')) &
...                         Q(name__startswith=F('category__pizzas__name'))
...                     ),
...                     then=Value(1)
...                 ),
...                 output_field=IntegerField(),
...             ),
...         ),
...         0,
...     ),
... )
>>> [p, p.uses_name_of_another_pizza_as_name_prefix for p in pizzas]
[
    (<Pizza: Hawaiian>, 0),
    (<Pizza: Hawaiian King>, 1),
    (<Pizza: Pepperoni>, 0),
]

Still, it doesn't allow for what I need precisely. So, I preferred to go with raw SQL.

Upvotes: 1

Related Questions