>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.
