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?

bensentropy
  • 1,293
  • 13
  • 17

1 Answers1

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 2018-06-29

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')
)
Community
  • 1
  • 1
Paolo Melchiorre
  • 5,716
  • 1
  • 33
  • 52
  • 1
    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. – Nad Jun 29 '18 at 15:51
  • @Nad 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 update – Paolo Melchiorre Jun 29 '18 at 16:49
  • 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 https://stackoverflow.com/questions/51105651/django-postgres-searchvectorfield-from-foreign-key – Nad Jun 30 '18 at 08:36
  • 1
    found an answer of yours to this here! https://stackoverflow.com/questions/42679743/is-it-possible-to-add-your-own-strings-to-a-django-searchvectorfield/47497078#47497078 Brilliant, thank you – Nad Jun 30 '18 at 08:50
  • @nad I'm you've found an useful answer for your problem. If it works please vote for it. – Paolo Melchiorre Jun 30 '18 at 09:38