invalid reference to FROM-clause entry for table

[hippomoe]

I'm using djorm-ext-pgfulltext extension to do full-text-search using Postgres on a title field for one of my models (Bookmark).

class Bookmark(TimeStampModel):
    title = models.CharField(max_length = 100)
    # other fields

    search_index = VectorField()

    objects = SearchManager(
        fields = ('title'),
        config = 'pg_catalog.english',
        search_field = 'search_index',
        auto_update_search_field = True
    )

I have another model called SharedBookmark that is OneToOne related to the Bookmark.

class SharedBookmark(TimeStampedModel):
    bookmark = models.OneToOneField(Bookmark)
    # other fields

I am able to do a search through my Bookmark instances using:

Bookmark.objects.search(query)

But when I try to do the following filter

SharedBookmark.objects.filter(bookmark__in=Bookmark.objects.search(query))

I receive the following error:

invalid reference to FROM-clause entry for table "bookmarks_bookmark" LINE 1: ...ECT U0."id" FROM "bookmarks_bookmark" U0 WHERE ( ("bookmarks... ^ HINT: Perhaps you meant to reference the table alias "u0"

This is the traceback:

Traceback:

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response

  115.                         response = callback(request, *callback_args, **callback_kwargs)

File "/home/derek/Development/skillfare/skillfare/bookmarks/views.py" in search

  76.                 print shared_bookmarks

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/models/query.py" in __repr__

  77.         data = list(self[:REPR_OUTPUT_SIZE + 1])

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/models/query.py" in __len__

  92.             self._result_cache.extend(self._iter)

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/models/query.py" in _safe_iterator

  344.             for item in iterator:

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/models/query.py" in iterator

  301.         for row in compiler.results_iter():

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in results_iter

  775.         for rows in self.execute_sql(MULTI):

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql

  840.         cursor.execute(sql, params)

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/backends/util.py" in execute

  41.             return self.cursor.execute(sql, params)

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py" in execute

  58.             six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])

File "/home/derek/.virtualenvs/skillfare/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py" in execute

  54.             return self.cursor.execute(query, args)

I can't make sense of the error. What causes this error and how should I fix it? 

Thanks for any advice!

hippomoe

[akaariai]

The reason for this error is that djorm-ext-pgfulltext doesn't support alias relabeling. The table aliases need to be relabeled when a query is used as a subquery (happen for bookmark__in=query). When this happens, all parts of the query that reference table aliases need to be relabeled so that the references continue to point to correct tables. A likely reason why this doesn't work in pgfulltext is that it uses .extra() somewhere, and .extra() doesn't support alias relabeling at all.

You can try to fix the bug in pgfulltext, or you could use bookmark__in=list(Bookmark.objects.search(...)). This will issue two separate queries.

 - Anssi

[hippomoe]

Anssi, 

Thank you for the explanation and recommendation.