Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#21208 closed Uncategorized (wontfix)

invalid reference to FROM-clause" for nested annotate query

Reported by: jaromudr@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Issue is very similar to #10182 wich was closed, the difference is only that I use extra query.
So I have next code:

    where = """            
            (setweight(to_tsvector('pg_catalog.english', coalesce("event_event"."name", "event_event"."description")), 'D')) @@ plainto_tsquery('pg_catalog.english', %s)
            OR (setweight(to_tsvector('pg_catalog.english', coalesce("event_singleevent"."description", '')), 'D')) @@ plainto_tsquery('pg_catalog.english', %s)
    """

    events = qs.extra(
        where=[where],
        params=[search_string, search_string]
    )

What I want to do is to select all tags from this events.

To do that I use next query

   tags = TaggedItem.objects.filter(object_id__in=events.values_list("event_id", flat=True)) 

and I got error
invalid reference to FROM-clause entry for table "event_event"
HINT: Perhaps you meant to reference the table alias "u3".

That occured because when we use such syntax django start subquery with

    SELECT U0."event_id" FROM "event_singleevent" U0 ...

I able to fix this error only next way:

    tags = TaggedItem.objects.filter(object_id__in=map(lambda event: event.event.id, events))

this solution have bad performance.

Please, can you give me some advise?

Environment: Django (1, 5, 2, 'final', 0) / PostgreSQL

Change History (3)

comment:1 by Anssi Kääriäinen, 10 years ago

Resolution: wontfix
Status: newclosed

This is unfortunate limitation of .extra(). If aliases are changed for the query (for example, it the query is used in subquery), then .extra() table references will be broken. The only option you have is to manually fix the extra clause for subquery condition.

So, you would have something like this:

    where = """            
            (setweight(to_tsvector('pg_catalog.english', coalesce("event_event"."name", "event_event"."description")), 'D')) @@ plainto_tsquery('pg_catalog.english', %s)
            OR (setweight(to_tsvector('pg_catalog.english', coalesce("event_singleevent"."description", '')), 'D')) @@ plainto_tsquery('pg_catalog.english', %s)
    """

    events = qs.extra(
        where=[where],
        params=[search_string, search_string]
    )
    where_for_subq = """            
            (setweight(to_tsvector('pg_catalog.english', coalesce(u3."name", u3."description")), 'D')) @@ plainto_tsquery('pg_catalog.english', %s)
            OR (setweight(to_tsvector('pg_catalog.english', coalesce(u4."description", '')), 'D')) @@ plainto_tsquery('pg_catalog.english', %s)
    """
    events_for_subq = qs.extra(
        where=[where_for_subq],
        params=[search_string, search_string]
    )
    tags = TaggedItem.objects.filter(object_id__in=events_for_subq.values_list("event_id", flat=True)) 

You will need to check correct aliases to use in where_for_subq.

There isn't much Django can do in this case - changing the aliases automatically inside raw SQL is practically impossible to do, and aliases must be changed when the query is used as subquery. So, wontfixing this ("cantfix" would be more accurate resolution).

comment:2 by anonymous, 10 years ago

I already tried this solution, it also does not work, when I change "event_event" to u3, t3 is used instead of u3 :)

comment:3 by Anssi Kääriäinen, 10 years ago

You can check the aliases used by the query by doing print(events.query).

If you need more assistance this isn't the right place for that - use #django IRC channel or django-users google group instead.

Note: See TracTickets for help on using tickets.
Back to Top