annotate django query by substring

Question

How do you annotate a query set using a substring from a field?

I presume something like this:

query_by_domain = (
    queryset
    .annotate(domain=Substr("email", F("email").Index("@") + 1))
    .values("domain")
    .annotate(count=Count("id"))
    .order_by("domain")
)

Answer 1

You can use StrIndex ^[Django-doc] and work with:

from django.db.models import Count, Value as V
from django.db.models.functions import StrIndex, Substr

queryset.values(domain=Substr('email', StrIndex('email', V('@')) + 1)).annotate(
    count=Count('id')
).order_by('domain')