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.
