invalid reference to FROM-clause" for nested annotate query

[jaromudr@…]

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

[Anssi Kääriäinen]

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).

[anonymous]

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

[Anssi Kääriäinen]

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.