common table expressions, i.e. CTE or WITH queries

[Mike Bayer]

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.

[Mike Bayer]

I'd love to do this one but coming up with a common syntax is still outside the realm of my mental capacity

[Mike Bayer]

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.

[Mike Bayer]

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 !

[Mike Bayer]

hooray oracle added WITH as of 9.2 !

http://www.oracle-base.com/articles/misc/WithClause.php

[Mike Bayer]

1607b74f8527905ecdc6133b4b4166a9ed675e09

[Mike Bayer]

Removing milestone: 0.7.6 (automated comment)