hi again,
after playing a while with PG, here is a SQL statement that outputs the
expected result
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 groups_recursive_id, 1
FROM groups_recursive,
groups_recursive_parents__groups_recursive_children
WHERE groups_recursive_parents__groups_recursive_children.children_id =
4
AND groups_recursive_parents__groups_recursive_children.parents_id
= groups_recursive.id
UNION
SELECT groups_recursive.id, rank+1
FROM all_parents, groups_recursive,
groups_recursive_parents__groups_recursive_children
WHERE
groups_recursive_parents__groups_recursive_children.children_id =
all_parents.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;
and FYI, here is what the code is issuing (not functionnal)
SELECT groups_recursive.id AS groups_recursive_id
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 = :groupid
UNION SELECT groups_recursive.id AS id, rank + :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
we have problems such as
column "1" does not exists
if we add groups_recursive in both FROM clauses
regards
Nil
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/cTkUMo18h_IJ.
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.
