How to create annotate expression, so it will use composite index?

[rocky wraith]

Howdy 👋
About my setup, django 3.2.12, postgres 13 with pg_trgm extension

class User(models.Model):
    first_name = models.CharField()
    last_name = models.CharField()

class Address(models.Model):
    user = models.ForeignKey(User)

create index concurrently idx_user_full_name on users using gin ((first_name || ' ' || last_name) gin_trgm_ops);

Here is the part that I have problem with, im not sure how to create expression here so it will use index. I was thinking about using RawSQL but im not sure how to code it so it wont look like spaghetti​

value = 'Agent Smith'
Address.objects.annotate(user_full_name= ? ).filter(user_full_name__iexact=value)

[inner venture]

You could try Concat. I would guess it evaluates to CONCAT and not || though. (Really wish the docs would include examples of the raw SQL) https://docs.djangoproject.com/en/5.0/ref/models/database-functions/#concat

[rocky wraith]

@inner venture thanks for response,
sadly Concat does not work as it does not match it with index, SQL is something like this Concat("firstname", Concat(" ", "last_name"))

[inner venture]

Looking at the Django source code it should use pipes. https://github.com/django/django/blame/790f0f8868b0cde9a9bec1f0621efa53b00c87df/django/db/models/functions/text.py#L76

Looks like it was actually a change made as of 9 months ago for precisely your application. https://code.djangoproject.com/ticket/34955

I'm not familiar with the Django ticket tracker, but it looks like you'll need to use the dev version of Django to get the change.

[fresh root]

Or possibly use a custom function? (untested)

class ConcatWithDelimiter(Func):
    function = '||'
    template = "%(expressions)s || %(delimiter)s || %(expressions)s"

    def __init__(self, exp1, exp2, delimiter, **extra):
        expressions = [exp1, delimiter, exp2]
        super().__init__(*expressions, **extra)

Address.objects.annotate(
    user_full_name=ConcatWithDelimiter(
        F('first_name'),
        F('last_name'),
        Value(' ')
    )
)

[rose torrent]

@fresh root your code would only work for this case only (when there are only 2 args), also F would resolve value, but you are close

you can do it by using RawSQL but you would also need do joins and resolve table column names too
try this

class PostgresConcatOperator(Func):
    template = "(%(expressions)s)"
    arg_joiner = "||"

Address.objects.alias(user_full_name=PostgresConcatOperator("user__first_name", Value(" "), "user__last_name")).filter(user_full_name__iexact="Agent Smith")

this would produce something like this and that would trigger your gin index

select *, ("u"."first_name"||" "||"u".last_name") as "user_full_name" from address left join users u on u.id = address.user_id where UPPER(("u"."first_name"||" "||"u".last_name")) = UPPER("Agent Smith")