On Aug 12, 2011, at 6:00 AM, NiL wrote:
> Hi,
>
> thank you for your help, and the effort at writing a good piece of code.
>
> however, while playing around with, and trying to execute it in PGAdmin, I
> stumbled upon some problems
>
> the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id)
>
> generates a SQL like (I've inserted real values):
>
> SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS
> groups_recursive_name, groups_recursive.display_name AS
> groups_recursive_display_name
> FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
> (SELECT groups_recursive.id AS id, "1"
> FROM groups_recursive_parents__groups_recursive_children
> WHERE groups_recursive.id =
> groups_recursive_parents__groups_recursive_children.parents_id
> AND groups_recursive_parents__groups_recursive_children.children_id = 4
> UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2
> FROM groups_recursive_parents__groups_recursive_children
> WHERE groups_recursive_parents__groups_recursive_children.children_id = id
> AND groups_recursive_parents__groups_recursive_children.parents_id =
> groups_recursive.id)
> )
> SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id
>
> it chokes on :
>
> ERROR: invalid reference to FROM-clause entry for table "groups_recursive"
> LINE 3: (SELECT groups_recursive.id AS id, "1"
> ^
> HINT: There is an entry for table "groups_recursive", but it cannot be
> referenced from this part of the query.
>
>
> ********** Error **********
>
> ERROR: invalid reference to FROM-clause entry for table "groups_recursive"
> SQL state: 42P01
> Hint: There is an entry for table "groups_recursive", but it cannot be
> referenced from this part of the query.
> Character: 242
>
> obviously, there is a problem at the JOIN stage
>
> ... SELECT groups_recursive.id AS id, "1"
> FROM groups_recursive_parents__groups_recursive_children
> WHERE ....
>
> I tried
>
> s = select([groups.c.id, column("1")],from_obj=groups)....
>
> to no better luck
OK so I'd need you to wrestle with the PG side here, and figure out exactly
what query PG accepts on this. I've little experience with CTEs, not sure if
perhaps the WITH always needs to be the outermost expression, etc.
Perhaps the query for the full span of mapped columns needs to be where we're
putting the "select * from all_parents" part, and it joins to "all_parents"
right there. The CTE construct will ultimately have to work that way anyway,
in that you can put any SELECT you want at that point.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
