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.
