I should have mentioned, I modified the CTE demo to work on SQL Server and I believe (I will double check this...) that on SQL Server the "with" of the CTE needs to be at the top of the statement and referenced in subqueries below. The generated SQL "SELECT .... FROM (WITH ...)" is invalid on SQL Server.
The goal is to render SQL similar to: WITH all_dates(date) AS ( SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, :step, date) AS "DATEADD_1" FROM all_dates WHERE DATEADD(dd, :step, date) <= CAST(:stop AS DATETIME) ) SELECT * FROM all_dates OPTION (MAXRECURSION 0) In order for this to work on SQL Server as a mapped class I believe it would need to be rendered similar to: WITH RECURSIVE all_dates(date) AS (SELECT CAST(%(start)s AS TIMESTAMP WITHOUT TIME ZONE) AS anon_2 UNION ALL SELECT DATEADD(dd, %(step)s, all_dates.date) AS "DATEADD_1" FROM all_dates WHERE DATEADD(dd, %(step)s, all_dates.date) <= CAST(%(stop)s AS TIMESTAMP WITHOUT TIME ZONE)) SELECT anon_1.date AS anon_1_date FROM (SELECT * FROM all_dates) AS anon_1 -Marc On Thu, Aug 18, 2011 at 11:47 AM, Michael Bayer <[email protected]>wrote: > > On Aug 18, 2011, at 11:23 AM, Marc DellaVolpe wrote: > > > I found the following CTE demo (http://www.sqlalchemy.org/trac/ > > attachment/ticket/1859/cte_demo.py) and I was wondering if there was > > any way to map these selects. > > > > I have built a CTE based select to generate a dates table on the fly > > and I would love to be able to map this and use generative selects to > > transform queries. I have only been able to make the mapping work > > with .from_statement() however this does not allow for > > transformations. > > > > Any thoughts? > > > > Thanks, > > -Marc > > > > with CommonTableExpression.create('all_dates', ['date']) as all_dates: > > > > start_exp = cast(bindparam('start'), DateTime) > > end_exp = cast(bindparam('stop'), DateTime) > > > > exp = func.DATEADD( literal_column('dd'), bindparam('step'), > > all_dates.c.date ) > > > > s1 = select([start_exp]) > > s2 = select([exp], from_obj=all_dates).where(exp <= end_exp) > > > > s = s1.union_all(s2) > > > > all_dates = SelectFromCTE(all_dates, s) > > > > class Date(object): > > query = Session.query_property() > > > > @classmethod > > def range(cls, start, stop, step=1): > > return > > cls.query.from_statement(str(all_dates)).params(start=start, > > stop=stop, step=step) > > > > mapper(Date, all_dates, primary_key=[all_dates.c.date]) > > > > print Date.range('2011-01-01 00:00:00', '2021-01-01 00:00:00').all() > > this maps fine for me (it's best to apply alias() to all_dates before > mapping), I just get a statement that doesn't work: > > SELECT anon_1.date AS anon_1_date > FROM (WITH RECURSIVE all_dates(date) AS > (SELECT CAST(%(start)s AS TIMESTAMP WITHOUT TIME ZONE) AS anon_2 UNION ALL > SELECT DATEADD(dd, %(step)s, all_dates.date) AS "DATEADD_1" > FROM all_dates > WHERE DATEADD(dd, %(step)s, all_dates.date) <= CAST(%(stop)s AS TIMESTAMP > WITHOUT TIME ZONE)) > > SELECT * FROM all_dates) AS anon_1 > > > (ProgrammingError) column "dd" does not exist > > what should "dd" be here ? > > > > > > > > > > > > > -- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > [email protected]. > > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
