I ran into a small problem attempting to union two existing Query instances
together, where one that was using some custom parameters seemed to lose the
parameter information when incorporated into a higher level query making
use of from_statement. This is with SA 0.5.8.
Given these two starting queries (s=sessionmaker()):
r_q = (s.query(Registration.class_id).
filter(Registration.child_id == self.child_id).
filter('(registrations.first_class, registrations.last_class+1) '
'overlaps (date :start_date, date :end_date)').
params(start_date=week_start, end_date=week_end))
m_q = (s.query(Makeup.class_id).
filter(Makeup.child_id == self.child_id).
filter(sql.between(sql.cast(Makeup.start_time, DATE),
week_start, week_end)))
week_start and week_end are two datetime objects.
The above both execute just fine on their own, and in the case of r_q,
the start_date and end_date parameters are properly replaced.
I was updating to use them together as a UNION ALL query. Initially I
did the straight forward:
r_q.union_all(m_q)
which does work fine. But it wraps the union in an extra select layer
with an auto-generate anonymous column, that I was curious if I could
get rid of. More from a what-if-I-needed-to question than concerns
about performance or anything.
Based on some prior email discussions I found I ended up using
something like (union_all=sqlalchemy.sql.union_all):
s.query('child_id').from_statement(union_all(r_q, m_q))
which when printed as a string produces just the query I was looking
for. However, during execution, in the parameters dictionary the
values for both start_date and end_date are None. This seems true if
I use the union with a pure select() operation as well, and also other
permutations such as "r_q.union(m_q).statement".
So then on a lark, I tried my original union_all query, but in the
opposite order (e.g,. m_q.union_all(r_q)) and got the same error. So
even the use of r_q in a plain nested select union seems a little
fragile, with the parameters only working if its the first query.
Is there a safe way to wrap r_q into larger queries without risking
the loss of the supplied parameters or something that can be done to
r_q itself to make the parameters "stickier"?
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.
