0

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.

Boris Burkov
  • 13,420
  • 17
  • 74
  • 109
  • The closest I could see was [this answer about doing your own F expressions](https://stackoverflow.com/a/28821724/1081569). If you could combine that with an SQL function to split strings, like [Postgres `substring(string from pattern)` or `split_part`](https://www.postgresql.org/docs/9.6/static/functions-string.html), maybe you could do it. It may be database specific, which you seem to want to avoid (no raw SQL), but I haven't seen a function in Django to split strings. – Paulo Almeida Aug 18 '17 at 00:40
  • @PauloAlmeida Thank you, Paulo, I think that's exactly what I need. – Boris Burkov Aug 18 '17 at 12:25

1 Answers1

1

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.

Boris Burkov
  • 13,420
  • 17
  • 74
  • 109
  • Nice! I had seen some things with `Concat`, but not `Coalesce`. That said, if I understand correctly, for your use case you would need a 'John' in the DB (with no surname) to look for namesakes of John, right? – Paulo Almeida Aug 18 '17 at 16:02
  • @PauloAlmeida Yep, this post doesn't solve my problem entirely - it counts namesakes, but doesn't return the list of them. `Coalesce` doesn't really play a big role here - it just provides a 0 default, I believe. I still haven't found a good way to do my self-join, unfortunately :) – Boris Burkov Aug 18 '17 at 17:13