SearchVectorField - joined reference not allowed for taggit field

Using Django Using the ORM
1

My model definition is as follows (a simplified model of the one being used in actual product. There are a coupe of other fields:

from django.db import models
from taggit.managers import TaggableManager

from django.contrib.postgres.search import SearchVectorField
from django.contrib.postgres.indexes import GinIndex

class Product(models.Model):
    tags = TaggableManager()
    search_vector = SearchVectorField(null=True)

    class Meta:
        indexes = [
            GinIndex(fields=['search_vector'])
        ]

I run make migrations and migrate commands. These work correctly.

I then run the following set of commands to build the index:

search_vector = SearchVector('tags__name')
Product.objects.all().update(search_vector=search_vector)

I end up getting the following error:

django.core.exceptions.FieldError: Joined field references are not permitted in this query

This is clearly caused by the tags__name field, but I am not sure how to solve it.

Can someone please explain what I would need to do in order to run the above commands correctly?

Thanks!

2

I think I’ve run across this before. I believe I’ve solved it using an annotate before. Try creating an annotated field, then using that in the update.

Product.objects.annotate(
    inline_search_vector=SearchVector('tags__name')
).update(search_vector=F('inline_search_vector'))
3

Thanks @CodenameTim ,

I still seem to be hitting upon the error when using that technique:

django.core.exceptions.FieldError: Joined field references are not permitted in this query

4

Hmm, maybe it was using a Subquery.

5

@zigzagdoom I was able to confirm the way I handled it was with a Subquery instance.

6

Thanks a lot @CodenameTim , I will take a look

7

@zigzagdoom were you able to solve this issue? I couldn’t make it work with a “Subquery” instance

8

@CodenameTim can you help with subquery for the above example.

9

From the docs example we have:

from django.db.models import OuterRef, Subquery
newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')
Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))

We’re not going to use it exactly, but it’ll be something close.

Let’s say Post.latest_activity is a new datetime field we’ve added and we want to update all Post instances to reflect the latest created_at from their comments.

newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')
Post.objects.annotate(
    latest_comment_datetime=Subquery(newest.values('created_at')[:1])
).update(
    latest_activity=F('latest_comment_datetime')
)
10

thanks for the quick response. tried to apply the subquery to the above post from @zigzagdoom brings out another error

since the tags is m2m, and a post can have multiple tags, restristicitng to one (using slice [:1]) doesn’t help.

11

If you would like help with your problem please include more information about your situation. A good question will include what you’re trying to do, what you’re expecting to see and what you’re actually encountering. If it’s code related, please share the code as well. If there’s an error, include the error message and stacktrace.

12

@CodenameTim thanks, I solved the problem with subquery and StringAgg.

For others who are directed here from google for postgres full text search adding an m2m field or django-taggit package for tags, here is how i solved the error join not permitted error (similar to what @zigzagdoom got)

django.core.exceptions.FieldError: Joined field references are not permitted in this query

when trying this code

search_vector = SearchVector('tags__name')
Product.objects.all().update(search_vector=search_vector)

I used a StringAgg and used value expresions to add to the SearchVector, something like below pseudocode

Product.objects.all().annotate(tagnames=StringAgg("tags__name", delimiter=", ")).update(search_vector=SearchVector("title", "description") + SearchVector(Value("tagnames")))
1 Like