OK also, this is ready to go in from my perspective, I don't make usage of CTEs
in my normal work at the moment, so hopefully those people here interested in
the feature can give this a review, maybe even try the patch, because this will
be it !
On Mar 1, 2012, at 12:40 PM, Michael Bayer wrote:
>
> On Mar 1, 2012, at 10:47 AM, Claudio Freire wrote:
>
>> On Thu, Mar 1, 2012 at 12:11 PM, A.M. <age...@themactionfaction.com> wrote:
>>> Well, the SQL standard points at WITH RECURSIVE which is more general
>>> anyway. W.R. is basically an inductive query loop construct (base case
>>> UNION induction step) where CONNECT BY only handles key-based tree
>>> retrieval, no?
>>>
>>> Also, basic WITH support (without RECURSIVE) would be much appreciated-
>>> that could offer more flexibility than FROM-subqueries and could open the
>>> door for W.R.
>>
>> Also, CTE support (recursive or not) would be easily implementable by
>> a special case of selectable, whose visitor only outputs the name, and
>> prepends to the string the "WITH blah AS bleh".
>>
>>
>> import sqlalchemy as sa
>> somestuff = sa.select(...).cte("somestuff")
>> somequery = sa.select( somestuff.c.somecolumn, somestuff.c.someval == 3)
>
> I can say quite literally that you read my mind, or vice versa, I gave a
> quick try with select().with_(), abandoned that and added "cte()" just like
> you said here. By using the compiler to find the CTEs, then tacking them
> onto the outermost SELECT at the end, the feature add affects virtually no
> existing code at all so this can go right in. There's probably a lot of
> ways to trip it up but using it carefully seems to lead to the correct
> results, testing two of the examples at
> http://www.postgresql.org/docs/8.4/static/queries-with.html . Both
> examples are tricky as the CTEs refer to themselves or each other.
>
> See the patch at http://www.sqlalchemy.org/trac/ticket/1859.
>
> Here's an example (note I left out the "name" here which you can of course
> put in, but even the anon_X thing works out):
>
> from sqlalchemy.sql import table, column, select, func
>
> parts = table('parts',
> column('part'),
> column('sub_part'),
> column('quantity'),
> )
>
> included_parts = select([parts.c.sub_part, parts.c.part, parts.c.quantity]).\
> 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.part, parts_alias.c.sub_part,
> parts_alias.c.quantity]).\
> where(parts_alias.c.part==incl_alias.c.sub_part)
> )
>
> s = select([included_parts.c.sub_part,
> func.sum(included_parts.c.quantity).label('total_quantity')]).\
> 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
>
>
>> I think this form makes a lot more sense in the SQLA API than the
>> context manager.
>
> yeah that was just a scratch idea.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@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.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.
