rendering of aliased CTE

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
Comments (3)
