try label the column in q2, say q2.maxgrade, then use that as print s.query(A).filter( A.grade==q2.maxgrade)...
On Tuesday 12 May 2009 10:17:11 Max Ischenko wrote: > I'm trying to express the following SQL: > > SELECT * FROM attendances a WHERE grade = (SELECT MAX(grade) FROM > attendances WHERE student_id=a.student_id) and school_id=112; > > >>> A2 = aliased(A) # A is Attendance class > >>> q2 = > >>> s.query(max_grade).filter(A.student_id==A2.student_id).subquery > >>>() print q2 # looks OK so far > > SELECT max(attendances.grade) AS max_1 > FROM attendances, attendances AS attendances_1 > WHERE attendances.student_id = attendances_1.student_id > > >>> print s.query(A).filter(and_(A.grade==q2, A.school_id==112)) > > SELECT attendances.id AS attendances_id, attendances.student_id AS > attendances_student_id, attendances.school_id AS > attendances_school_id, attendances.school_year_id AS > attendances_school_year_id, attendances.grade AS attendances_grade > FROM attendances, (SELECT max(attendances.grade) AS max_1 > FROM attendances, attendances AS attendances_1 > WHERE attendances.student_id = attendances_1.student_id) AS anon_1 > WHERE attendances.grade = SELECT max(attendances.grade) AS max_1 > FROM attendances, attendances AS attendances_1 > WHERE attendances.student_id = attendances_1.student_id AND > attendances.school_id = %(school_id_1)s > # doesn't work: > LINE 8: WHERE attendances.grade = SELECT max(attendances.grade) AS > m... > > If I do not use .subquery() it doesn't do correct thing either: > >>> q3 = s.query(max_grade).filter(A.student_id==A2.student_id) > >>> print s.query(A).filter(and_(A.grade==q3, A.school_id==112)) > > SELECT attendances.id AS attendances_id, attendances.student_id AS > attendances_student_id, attendances.school_id AS > attendances_school_id, attendances.school_year_id AS > attendances_school_year_id, attendances.grade AS attendances_grade > FROM attendances > WHERE attendances.grade = %(grade_1)s AND attendances.school_id = % > (school_id_1)s > > Pls help, > Max. > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
