There seems to be a problem with the custom compilation. >>> print Session.query(Date).order_by(Date.date.desc())
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) On Thu, Aug 18, 2011 at 1:08 PM, Marc DellaVolpe <[email protected]>wrote: > From what I can tell from > http://msdn.microsoft.com/en-us/library/ms190766.aspx and experimentally, > you can only put CTE's at the top: > > -- Works > WITH all_dates(date) AS ( > SELECT CAST('2011-01-01' AS DATETIME) AS anon_1 UNION ALL SELECT > DATEADD(dd, 1, date) AS "DATEADD_1" > FROM all_dates > WHERE DATEADD(dd, 1, date) <= CAST('2012-01-01' AS DATETIME) > ) > select * from (SELECT * FROM all_dates) as x > OPTION (MAXRECURSION 0) > > -- Generates an invalid syntax error > select * from ( > WITH all_dates(date) AS ( > SELECT CAST('2011-01-01' AS DATETIME) AS anon_1 UNION ALL SELECT > DATEADD(dd, 1, date) AS "DATEADD_1" > FROM all_dates > WHERE DATEADD(dd, 1, date) <= CAST('2012-01-01' AS DATETIME) > ) > ) as x > OPTION (MAXRECURSION 0) > > > Thank you for the solution. One approach I was considering was subclassing > Select, adding a method to attach a CTE to the select and then generating > custom SQL for the subclass but I wasn't completely familiar of the > compilation workings to determine if this was workable solution to force the > CTE to the top of the generated SQL. > > -Marc > > On Thu, Aug 18, 2011 at 12:13 PM, Michael Bayer > <[email protected]>wrote: > >> >> On Aug 18, 2011, at 11:56 AM, Marc DellaVolpe wrote: >> >> 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. >> >> >> Just to confirm, the "WITH RECURSIVE" can never be nested inside of any >> kind of subquery with SQL Server (which would not be surprising given SQL >> Servers standard MO), is that correct ? >> >> That blows away a large amount of "generations" right there with Query >> since subqueries are a core part of its operation. >> >> As the ticket states, the CTE logic would probably need to be inside of >> the compilation of Select itself. >> >> The mapper itself only knows how to select "columns" from a selectable >> given, such as a table or other SELECT statement. So if "SELECT >> myexpr.date FROM (WITH RECURSIVE)" is impossible, either you have to stick >> to using your from_statement() approach, or you'd need to modify the >> compilation of Select() such that it generates *nothing* if the thing being >> selected from is a CTE, which is quite awkward, surprising, and I can't see >> us ever having it do that by default, but here's that: >> >> >> from sqlalchemy.sql.expression import Select >> >> @compiles(Select) >> def _dont_render_outside_of_cte(element, compiler, **kw): >> if element._froms: >> expr = element._froms[0] >> else: >> expr = None >> if isinstance(expr, SelectFromCTE): >> return compiler.process(expr, **kw) >> else: >> return compiler.visit_select(element, **kw) >> >> the mapping + query: >> >> class Date(Base): >> __table__ = all_dates >> __mapper_args__ = {'primary_key':(all_dates.c.date)} >> >> @classmethod >> def range(cls, start, stop, step=1): >> return Session().query(Date).params(start=start, stop=stop, >> step=step) >> >> Session = scoped_session(sessionmaker()) >> print Date.range('2011-01-01 00:00:00', '2021-01-01 00:00:00') >> >> produces: >> >> WITH RECURSIVE all_dates(date) AS >> SELECT CAST(:start AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, >> :step, all_dates.date) AS "DATEADD_1" >> FROM all_dates >> WHERE DATEADD(dd, :step, all_dates.date) <= CAST(:stop AS DATETIME) >> >> SELECT * FROM all_dates >> >> >> >> >> >> >> 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. >> >> >> -- >> 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.
