Re: [sqlalchemy] aliasing a CTE expression

[Michael Bayer]

On Jul 15, 2013, at 1:58 PM, Rob  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 ' 
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] aliasing a CTE expression

[Rob]

(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 ' 
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?

Many thanks,
Rob

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.