On Apr 6, 2012, at 10:17 AM, Oltmans wrote:
> Greetings, I hope all are well. First off, all I'm sorry that my SQL
> isn't great.
>
> I've a raw SQL that looks like following
>
> select a.appointmentId, a.patientId, r.MaxTime from (
> select appointmentid, patientid, max(apptDate) as MaxTime
> from appointment
> where facilityid=95
> group by patientid
> ) r
> inner join
>
> Appointment a on
> a.patientid = r.patientid and
> a.apptDate = r.MaxTime
>
>
> I'm using declarative style of SQLAlchemy in my code and here is how
> my query looks like
>
> appt_query = alchemy_session.query(Appointment.appointmentId,
> Appointment.patientId,
> func.max(Appointment.apptDate).label('maxTime')).filter(
>
> Appointment.facilityId == 95,
>
> ).group_by(Appointment.patientId).subquery()
>
>
> appointments = alchemy_session.query(Appointment.appointmentId,
> Appointment.patientId, appt_query.c.maxTime).outerjoin(
>
> appt_query, and_(
> Appointment.patientId == appt_query.c.patientId,
> Appointment.apptDate == appt_query.c.maxTime
> )
>
> )
>
> but when I do
>
> print appointments
>
> It's unfortunately not producing the SQL that I want. I know there are
> lapses in my understanding of SQL so any pointers on this will be
> really helpful. Thank you for your time and help.
if you want the Appointment on the outside of the join to be aliased with a
symbol like "a", you'd use a = aliased(Appointment, "a"), then use "a" as the
thing you're joining to.
The SQL also seems to be selecting from <subquery JOIN a>, but I'm guessing you
really want it to be OUTER JOIN in which case that order is significant, so you
could say:
a = aliased(Appointment, "a")
q = sess.query(...).select_from(appt_query).join(a,
and_(a.patientid==appt_query.c.patient_id, a.apptdate==appt_query.c.maxtime))
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
