Is it possible to persist a joined field in Djangos SearchVectorField?
Asked Answered
T

1

7

Is it possible to persist a joined field with Djangos SearchVectorField for full text search?

For example:

class P(models.Model):
    brand = models.ForeignKey(Brand, on_delete=models.CASCADE)
    search_vector = SearchVectorField(null=True, blank=True)

code:

p = P.objects.get(id=1)
p.search_vector = SearchVector('brand__name')
p.save()

raises this exception:

FieldError: Joined field references are not permitted in this query

If this is not possible how can you increase the performance of joined annotated queries?

Thallic answered 24/8, 2017 at 14:6 Comment(0)
V
7

I found a workaround to your issue:

p = P.objects.annotate(brand_name=SearchVector('brand__name')).get(id=1)
p.search_vector = p.brand_name
p.save()

Update 2023-11-23

As reported in official documentation:

If you’re just updating a record and don’t need to do anything with the model object, the most efficient approach is to call update(), rather than loading the model object into memory.

Using update() also prevents a race condition wherein something might change in your database in the short period of time between loading the object and calling save().

Finally, realize that update() does an update at the SQL level and, thus, does not call any save() methods on your models, nor does it emit the pre_save or post_save signals (which are a consequence of calling Model.save()).

So in this case you can use this query to perform a single SQL query on the database:

from django.contrib.postgres.search import SearchVector
from django.db.models import F

P.objects.annotate(
    brand_name=SearchVector('brand__name')
).filter(
    id=1
).update(
    search_vector=F('brand_name')
)

Or a shorter form (suggested by @christophe31) is:

from django.contrib.postgres.search import SearchVector

P.objects.filter(id=1).update(search_vector=SearchVector('brand__name))

Both above solutions generate the same SQL code in the database.

Very answered 1/9, 2017 at 12:46 Comment(6)
aren't you making a redundant db query with that .get(id=1)? I'm also looking for a solution to build a search vector from a foreign relationship.Ellon
@Ellon I've updated my answer with a solution that perform one query only on db, but read the documentation about the difference between save and updateVery
thanks for this. My issue is I already have an instance of an model and for which I want to update the search_vector from a post_save signal. I posted a question here #51106151Ellon
found an answer of yours to this here! #42680243 Brilliant, thank youEllon
@nad I'm you've found an useful answer for your problem. If it works please vote for it.Very
P.objects.filter(id=1).update(search_vector=SearchVector("brand__name")) is enoughPhotobathic

© 2022 - 2024 — McMap. All rights reserved.