common table expressions, i.e. CTE or WITH queries
http://www.google.com/url?sa=D&q=http://www.postgresql.org/docs/8.4/interactive/queries-with.html
http://en.wikipedia.org/wiki/Common_table_expressions
http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f380e277af/fb179a515bf48868
http://groups.google.com/group/sqlalchemy/browse_thread/thread/4f7b243c41eb183f/fb79736d4c435537
one challenge here is deciding what to do with Oracle's entirely non-standard CONNECT BY approach. syntactically converting between a CTE and connect by is eluding me, so the initial solution may have to skip Oracle.
Comments (8)
-
reporter -
reporter - changed title to common table expressions, i.e. CTE or WITH queries
-
reporter Attached is a recipe given to a user to generate a Postgresql CTE. The usage of "with" should definitely be part of the solution as it flows very naturally.
-
reporter OK we decided we'll at least get the SQL expression system to do CTEs, so the attached patches/demos illustrate this using a new
CTE()
construct.We're doing the PG examples illustrated at: http://www.postgresql.org/docs/8.4/static/queries-with.html. MSSQL would appear to use the same syntax.
It works in a very literal style, so for the recursive examples you still need to really lay it all out, but even anonymous aliases work:
from sqlalchemy.sql import table, column, select, func parts = table('parts', column('part'), column('sub_part'), column('quantity'), ) included_parts = select([parts.c.part, parts.c.quantity](parts.c.sub_part,)).\ where(parts.c.part=='our part').cte(recursive=True) incl_alias = included_parts.alias() parts_alias = parts.alias() included_parts = included_parts.union( select([parts_alias.c.sub_part, parts_alias.c.quantity](parts_alias.c.part,)).\ where(parts_alias.c.part==incl_alias.c.sub_part) ) s = select([ func.sum(included_parts.c.quantity).label('total_quantity')](included_parts.c.sub_part,)).\ group_by(included_parts.c.sub_part) print s
output:
WITH RECURSIVE anon_1(sub_part, part, quantity) AS ( (SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity FROM parts WHERE parts.part = :part_1 UNION SELECT parts_1.part AS part, parts_1.sub_part AS sub_part, parts_1.quantity AS quantity FROM parts AS parts_1, anon_1 AS anon_2 WHERE parts_1.part = anon_2.sub_part) ) SELECT anon_1.sub_part, sum(anon_1.quantity) AS total_quantity FROM anon_1 GROUP BY anon_1.sub_part
"anon_1 AS anon_2", that's pretty crazy !
-
reporter - changed milestone to 0.7.6
-
reporter hooray oracle added WITH as of 9.2 !
-
reporter - changed status to resolved
-
reporter - removed milestone
Removing milestone: 0.7.6 (automated comment)
- Log in to comment
I'd love to do this one but coming up with a common syntax is still outside the realm of my mental capacity