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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
