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 <mike...@zzzcomputing.com>
> 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 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.
>
>
>
> --
> 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.
