Cannot use `pgtrigger.UpdateSearchVector` across relations

[JackAtOmenApps]

I tried to add a trigger to update a SearchVectorField with a field that has a relation to another table

class MyModel(models.Model):
    search_vector = SearchVectorField()

    class Meta:
        triggers = [
            pgtrigger.UpdateSearchVector(
                name="add_fields_to_entry_vector",
                vector_field="search_vector",
                document_fields=["provider__name", "consumer"],
            )
        ]

But I got...

django.core.exceptions.FieldDoesNotExist: Entry has no field named 'provider__name'

I assume this is similar to #47 and is not currently possible?

[wesleykendall]

@OmenApps underneath the hood, the UpdateSearchVector trigger calls tsvector_update_trigger, which is provided by Postgres. This procedure only allows for columns from the row to be used, i.e. you can't join in another table like that.

For your use case, you will likely need to write a custom trigger that queries that relationship. It will need to re-implement what tsvector_update_trigger does internally. Unfortunately I'm not familiar enough with how tsvector_update_trigger is implemented.

I'll keep this ticket open for now in case anyone might have ideas of how to implement it as a raw trigger. If someone provides a working version, I'd be happy to consider extending the UpdateSearchVector trigger to support this use case

[jokiefer]

i have a similar use case in my project. I used the new implemented GeneratedField to generate the SearchVectorField on the fly and run into the similar issue. Postgres can't generate the column based on related columns.

So my first workaround was to store the tags inside a local ArrayField, to use it for the generating expression like this:

class Metadata(models.Model):
   title: str = models.CharField(max_length=1000,
                                  verbose_name=_("title"),
                                  help_text=_(
                                      "a short descriptive title for this metadata"),
                                  default="")
    abstract = models.TextField(verbose_name=_("abstract"),
                                help_text=_(
                                    "brief summary of the content of this metadata."),
                                default="")
   keywords = models.ManyToManyField(to=Keyword)
   keywords_list = ArrayField(
        base_field=models.CharField(max_length=300),
        default=list,
        editable=False,
    )
   search_vector = GeneratedField(
        expression=SearchVector(
            F("title"),
            F("abstract"),

            Func(
                "keywords_list",
                function="array_to_tsvector",
                output_field=SearchVectorField()
            ),
            config="english"),
        output_field=SearchVectorField(),
        db_persist=True
    )

But in my case i still need the m2m relation to allow querying against different models with the same keywords.

So the keywords and keywords_list needs to be in sync.

Is it possible to sync the local keywords_list field on changes on the keywords m2m field with a trigger?