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

suppose have basic user model name , age:

class user(models.model):     name = charfield()  # e.g. john smith     age = integerfield()  # e.g. 21 

i want filter users 18+ y/o , each of them add special attribute namesakes (for instance, "john smith" ["john williams", "john neumann", ...]).

i know, how 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 in one-ish request? ideally, each user object in queryset i'd create custom attribute namesakes contained queryset of namesakes. similar annotate() or prefetch_related() to_attr do, not exactly.

i'd prefer avoid using raw sql, if possible.

thank you.

i found blog post, doing similar need and.... dear god...

>>> django.db.models import f, q, case, integerfield, sum, value, when >>> 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 p in pizzas] [     (<pizza: hawaiian>, 0),     (<pizza: hawaiian king>, 1),     (<pizza: pepperoni>, 0), ] 

still, doesn't allow need precisely. so, preferred go raw sql.