sure, couple of small adjustments, attached
On Aug 12, 2011, at 10:44 AM, NiL wrote: > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en.
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
On Aug 12, 2011, at 10:44 AM, NiL wrote: hi again, |
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import FromClause
class CommonTableExpression(FromClause):
"""Represent the 'inside' of a common table
expression."""
named_with_column = True
def __init__(self, name, colnames):
self.name = name
self.colnames = colnames
def _populate_column_collection(self):
for name in self.colnames:
c = column(name)
c.table = self
self._columns.add(c)
@classmethod
def create(cls, name, colnames):
target = CommonTableExpression(name, colnames)
class ctx(object):
def __enter__(self):
return target
def __exit__(self, *arg, **kw):
pass
return ctx()
class SelectFromCTE(FromClause):
"""Represent the 'outside' of the CTE.
Ultimately this would be integrated into Select
itself, since we just want a Select with an
extra clause on top. "CommonTableExpression" objects
would be pulled from the FROM clause
and rendered on top.
"""
def __init__(self, inner_thing, stmt):
self.inner_thing = inner_thing
self.stmt = stmt
def _populate_column_collection(self):
for name, c in zip(self.inner_thing.colnames, self.stmt.c):
c._make_proxy(self, name)
@compiles(CommonTableExpression)
def _recur_inner_thing(element, compiler, **kw):
return element.name
@compiles(SelectFromCTE)
def _recur_outer_thing(element, compiler, **kw):
text = (
"WITH RECURSIVE %s(%s) AS \n"
"%s\n"
"\n"
"SELECT * FROM %s" % (
element.inner_thing.name,
", ".join(element.inner_thing.colnames),
compiler.process(element.stmt, **kw),
element.inner_thing.name
)
)
if kw.get('asfrom'):
text = "(%s)" % text
return text
if __name__ == '__main__':
from sqlalchemy import select, Integer, String
from sqlalchemy.sql.expression import table, column, FromClause, bindparam, literal_column
groups = table('groups_recursive', column('id', Integer),
column('name', String),
column('display_name', String))
groups_assoc = table('groups_recursive_parents__groups_recursive_children',
column('parents_id'), column('children_id'))
with CommonTableExpression.create("all_parents", ["id", "rank"]) as all_parents:
rank = literal_column("rank")
s = select([groups.c.id, column("1")]).\
where(groups.c.id==groups_assoc.c.parents_id).\
where(groups_assoc.c.children_id==bindparam("groupid")).\
correlate(None).\
union(
select([groups.c.id, rank + 1]).\
where(groups_assoc.c.children_id==all_parents.c.id).\
where(groups_assoc.c.parents_id==groups.c.id).\
correlate(None)
)
all_parents = SelectFromCTE(all_parents, s)
# take a look
print all_parents
# make sure cols line up, typing info is transferred, etc.
assert all_parents.c.id.shares_lineage(s.c.id)
assert isinstance(all_parents.c.id.type , Integer)
print "\n-------------------------------------\n\n"
from sqlalchemy.orm import Session, mapper
class Group(object):
pass
mapper(Group, groups, primary_key=[groups.c.id])
all_parents = all_parents.alias()
q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id)
print q
