[Sqlalchemy-tickets] [sqlalchemy] #2783: rendering of aliased CTE

[sqlalchemy <mi...@zz...>]

#2783: rendering of aliased CTE
--------------------+------------------------------------
 Reporter:  zzzeek  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  high    |       Milestone:  0.8.xx
Component:  sql     |        Severity:  major - 1-3 hours
 Keywords:          |  Progress State:  in queue
--------------------+------------------------------------
 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class Semester(Base):
     __tablename__ = 'semesters'

     id = Column(Integer, primary_key=True)
     start_date = Column(Date)
     end_date = Column(Date)

 class Student(Base):
     __tablename__ = 'students'

     id = Column(Integer, primary_key=True)
     start_date = Column(Date)
     n_weeks = Column(Integer)


 S = Student.__table__.alias("S")
 s1 = select([
                 Semester.id.label("semester_id"),
                 func.generate_series(
                         Semester.start_date,
                         Semester.end_date, "1 day").label("day_date")
             ]).alias("day_series")

 semester_days = select([
             s1.c.semester_id,
             func.row_number().over().label("day_number"),
             s1.c.day_date]).order_by(s1.c.day_date).cte("semester_days")

 # if you alias this, then the CTE doesn't render
 SD_start = semester_days #.alias("SD_start")
 SD_end = semester_days.alias("SD_end")


 s2 = select([
             S.c.id.label("student_id"),
             S.c.start_date,
             SD_start.c.semester_id.label("start_semester_id"),
             S.c.n_weeks,
             SD_end.c.day_date.label("end_date"),
             SD_end.c.semester_id.label("end_semester_id")
         ]).select_from(
             S.join(SD_start, S.c.start_date == SD_start.c.day_date).
             join(SD_end, SD_end.c.day_number == SD_start.c.day_number + (7
 * S.c.n_weeks))
         ).order_by(S.c.start_date)

 print s2
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2783>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python