Tuesday, July 30, 2013

Re: invalid reference to FROM-clause entry for table

Anssi, 

Thank you for the explanation and recommendation. 

On Tuesday, July 30, 2013 3:49:21 AM UTC-5, akaariai wrote:


On Tuesday, July 30, 2013 6:40:27 AM UTC+3, hippomoe wrote:

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

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

No comments:

Post a Comment