That's a fair point. I think one interface might be casting a FromClause into a WithClause similar to how one would alias something.
With postgres it seams like when going from a WITH to WITH recursive is adding a UNION ALL and the recursive term. Throwing out an idea for an interface (for postgres at least): Say you want to make WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) First: included_parts = with_([parts.c.subpart, parts.c.part, parts.c.quantity], whereclause=parts.c.part=='our_product') # could also cast a SelectClause (or maybe even selectable) to a with_ by using selectable.with_() included_parts would compile to "WITH include_parts AS (SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product')" then we do: included_parts = base.recursive( [included_parts.c.sub_part, included_parts.c.part, included_parts.c.quantity], whereclause=included_parts.c.part==parts.c.part, all=True) Which would compile to the desired with clause. then a select(included_parts.c.part) would give you: WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT included_parts.part, Where can I find information on the Oracle syntax and other ones you'd like to support? On Jul 8, 11:46 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > I'd really be interested in defining a system that covers WITH / RECURSIVE > entirely, and makes sense both with PG / SQL server as well as Oracle. The > work here is figuring out what is common about those two approaches and what > an API that is meaningful for both would look like. Implementation and > tests are just the grunt work here. > > On Jul 8, 2010, at 2:03 PM, Mike Lewis wrote: > > > > > I'd be interested in prototyping a WithClause or something similar if > > you think it might be useful. > > > I imagine it would have similar semantics to a FromClause but would be > > prepended to the query. Currently, I'm not too interested in > > prototyping the RECURSIVE part and only care about Postgres. > > > For me to implement this would it be possible to do this in a non- > > intrusive manner (outside of modifying core SA code?) I'd guess it > > would have similar semantics to how select() will automatically > > include FromClauses that for columns that reference them. > > > Also, it would probably chain the WITHs automatically too. > > > Any thoughts? > > > Thanks, > > Mike > > > On Jul 6, 4:31 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > >> this is the current status of that: > > >>http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f... > > >> WITHand OVER seem to be the upcoming things we'll have to work on (PG, > >> MSSQL, DB2 supportWITH). Oracle makesWITHdifficult. Its also getting > >> to be time to do a DB2 dialect. > > >> On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote: > > >>> Does SA support this syntax? > > >>>http://www.postgresql.org/docs/9.0/static/queries-with.html > > >>> Thanks, > >>> Mike > > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "sqlalchemy" group. > >>> To post to this group, send email to sqlalch...@googlegroups.com. > >>> To unsubscribe from this group, send email to > >>> sqlalchemy+unsubscr...@googlegroups.com. > >>> For more options, visit this group > >>> athttp://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 sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://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 sqlalch...@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.