Hi everyone. I have been working in a little class that brings support
for "with recursive" idiom in my project. Actually the part it supports
for the moment are the necessary bits to generate hierarchical data (I
thought somebody might find it useful too so I added it as a recipe in
the wiki[1]).
The basic idea is that you submit a select expression like (see the wiki
for the whole example):
select([category.c.id, category.c.name])
and you will get a query like
with recursive rec as (SELECT category.id, category.name, 1 AS level,
ARRAY[id] AS connect_path
FROM category
WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
category.name, rec.level + 1 AS level, array_append(rec.connect_path,
category.id) AS connect_path
FROM category, rec
WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
rec.connect_path, case connect_path <@ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
that will give you the same information you requested plus some extra
columns with hierarchy related info.
The final piece I'm missing is how to pass a where clause: actually I
cannot make the final sql instruction to accept the parameters I'm
passing and after hours of trying it seems my sqlalchemy-fu is exhausted
and I can't fix it by myself.
To illustrate the problem, this is the select with a where clause:
select([category.c.id, category.c.name], category.c.active==True)
this is the query it generates:
with recursive rec as (SELECT dummy_hierarchy.id, 1 AS level, ARRAY[id]
AS connect_path
FROM dummy_hierarchy
WHERE dummy_hierarchy.active = %(active_1)s AND coalesce(parent_id, 0) =
0 UNION ALL SELECT dummy_hierarchy.id, rec.level + 1 AS level,
array_append(rec.connect_path, dummy_hierarchy.id) AS connect_path
FROM dummy_hierarchy, rec
WHERE dummy_hierarchy.active = %(active_1)s AND
dummy_hierarchy.parent_id = rec.id) SELECT rec.id, rec.level,
rec.connect_path, case connect_path <@ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
and this is the error I'm getting (if I run with nosetest):
Traceback (most recent call last):
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/nose-0.11.3-py2.6.egg/nose/case.py",
line 186, in runTest
self.test(*self.arg)
File "/home/mariano/Code/insite/dev/insite/insite/tests/test1_hierarchy.py",
line 261, in test8_where_clause
rs = Session.execute(qry).fetchall()
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/scoping.py",
line 129, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/session.py",
line 737, in execute
clause, params or {})
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py",
line 1109, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py",
line 1186, in _execute_clauseelement
return self.__execute_context(context)
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py",
line 1215, in __execute_context
context.parameters[0], context=context)
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py",
line 1282, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
"/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/default.py",
line 277, in do_execute
cursor.execute(statement, parameters)
KeyError: 'active_1'
Running from the python shell I get a totally different error but it's still
about the bind parameter (it does not fail but
it doesn't take the bind value either):
>>> Session.execute(x.compile()).fetchall()
with recursive rec as (SELECT category.id, category.name, 1 AS level, ARRAY[id]
AS connect_path
FROM category
WHERE category.active = %(active_1)s AND coalesce(parent_id, 0) = 0 UNION ALL
SELECT category.id, category.name, rec.level + 1 AS level,
array_append(rec.connect_path, category.id) AS connect_path
FROM category, rec
WHERE category.active = %(active_1)s AND category.parent_id = rec.id) SELECT
rec.id, rec.name, rec.level, rec.connect_path, case connect_path <@
lead(connect_path, 1) over (order by connect_path) when true then false else
true end AS is_leaf
FROM rec order by connect_path
{'active_1': None}
[]
The tar file in the wiki includes the whole class (+/- 200 lines of code but at
least half of
them are comments and doc strings) plus a test suite with 7 tests demonstrating
the usage.
If somebody has the time and the knowledge to help me out with this, I will
really appreciate it.
TIA,
Mariano
[1] http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PgsqlRecursive
--
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.
