- changed milestone to 0.7.xx
rendering of aliased CTE
Issue #2783
resolved
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)
-
reporter -
reporter - changed status to resolved
-
reporter - removed milestone
Removing milestone: 0.7.xx (automated comment)
- Log in to comment