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.
