On Jul 15, 2013, at 1:58 PM, Rob <[email protected]> wrote:
> (using sqlalchemy version 7.10 & postgres), I'm attempting to join a CTE
> twice in the same select statement.
>
> see here for a SQLFiddle, which shows the full select statement which I'm
> trying to build but essentially the problem I'm facing is how to create
> aliases for the `WITH` such that
> a second join is able to reference the first -- the emboldened 'AS <alias>'
> below, highlights the problem.
>
> WITH semester_days AS ( ... blah ... )
> SELECT ... blah ...
> FROM
> students as S
> JOIN
> semester_days as SD_start
> On SD_start.day_date = S.start_date
> JOIN
> semester_days as SD_end
> On SD_end.day_number = SD_start.day_number + (7 * S.n_weeks)
>
> Attempting to use:
> my_cte = select([ ... ]).cte(name='semester_days')
> followed by:
> cte_start = my_cte.alias('start_date')
> cte_end = my_cte.alias('end_date')
>
> ... in ether a .select_from() appended to the select clause, or in a
> `from_obj` within the clause, simply blows the `with` statement off the top
> of the select. (if that makes any sense)
>
> Is there any way to accomplish this?
I had to reconstruct your query fully in order to reproduce this, just for now
make sure you use a version of the CTE without an extra alias() assigned, in
addition to the aliased usage. That is, use alias("SD_end") but don't use
alias("SD_start") (or vice versa).
Ticket http://www.sqlalchemy.org/trac/ticket/2783 has been added.
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
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
