Hopefully this won't just be a section in the manual I missed, but I'm having some difficulties constructing an ORM query while using correlated sub-queries within filter(). This is with SA 0.5.8.
Given the following tables (with just the relevant columns shown): attendance ( class_id integer, start_time timestamp ) registrations ( class_id integer, first_class date, last_class date ) with mapped classes Attendance and Registration, I'd like to construct a query resulting in the SQL: select distinct attendance.start_time from attendance where attendance.class_id = :id: and cast(attendance.start_time as date) between :start: and :end: and * (select count(*) from attendance a1 * where a1.class_id = attendance.class_id and * a1.start_time = attendance.start_time) = * (select count(*) from registrations r * where r.class_id = attendance.class_id and * cast(attendance.start_time as date) between * r.first_class and r.last_class) order by attendance.start_time; (Where :id:, :start:, and :end: are parameters in the code). That is, I'm retrieving unique attendance start times for a specific class and date range, for which there are an equal number of attendance and registration records. An attendance record is for a single start timestamp but a registration record covers a date range. The basic query (no sub-query clause) is no problem, as (self.s is current SA session): q = (self.s.query(Attendance.start_time).distinct(). filter(Attendance.class_id == self.class_.class_id). filter(sql.between(sql.cast(Attendance.start_time, DATE), self.start_date, last_class)). order_by(Attendance.start_time)) The issue comes trying to include the additional where clause with the sub-queries. Amidst other attempts to include the sub-queries directly in the above statement, I've also separated out the sub-queries as: att_alias = aliased(Attendance) att = (self.s.query(sql.func.count(att_alias.child_id)). filter(att_alias.class_id == Attendance.class_id). filter(att_alias.start_time == Attendance.start_time)) reg = (self.s.query(sql.func.count(Registration.child_id)). filter(Registration.class_id == Attendance.class_id). filter(sql.between(sql.cast(Attendance.start_time, DATE), Registration.first_class, Registration.last_class))) If needed, I've tested using correlate() to the att query to match up the references to attendance, in case incorporating it properly into the main statement didn't correlate it automatically (which I figured it would). But various attempts to use these queries in a filter() portion of the original query always result ArgumentError exception that "filter() argument must be of type sqlalchemy.sql.ClauseElement or string" I figured I'm missing some magic method to permit turning "att == reg" into a ClauseElemnt somehow, but my attempts so far haven't worked. So for the moment I'm supplying the additional sub-query clause as pure text. Is this possible at the ORM layer, or do I need to drop down to the SQL layer in some way for that part of the clause? The examples I've been able to find of correlated sub-queries in the ORM all seem to approach the problem as a join issue, but I'd prefer to stick with the above SQL target if possible, which yields a significantly more efficient execution plan (though granted, that may be ephemeral over time). Even if I do change to joins later, I figure it's worth identifying how to do the above, if it's possible. Thanks. -- David -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.