Has anyone generated ORM queries using the OVERLAPS SQL operator that
reference columns in the tables in the query? I've been experimenting
with various approaches and can't seem to cleanly get the column names
(with their appropriate alias based on the rest of the query) into the
overlaps clause.
I'm basically issuing an ORM query and want to check that the date
range given by two columns in one of the objects being queried is
overlaps with a computed date range. In some cases the object whose
columns I am checking is the primary target of the query whereas in
others it's a joined class.
I found an older post from March where Michael suggested the form
<somexpression>.op('OVERLAPS', <someotherexpression>)
but I can't figure out how to apply that, and in particular what sort
of expression will produce a tuple at the SQL layer, yet still support
the "op" method? Namely the output needs to be of the form:
(start, stop) OVERLAPS (start, stop)
So I figured I'd try straight text, and was attempting something like:
query(MappedClass).
filter('(:c_start, :c_end) overlaps (:start, :end)').
params(c_start=MappedClass.start_col, c_end=MappedClass.end_col,
start=<datetimevalue>, end=<datetimevalue>)
but I'm having trouble identifying an appropriate value for the
c_start/c_end params to generate the column names in the resulting
SQL. The above gives can't adapt the InstrumentedAttribute references
in the params.
In the meantime I can fall back to a pure textual filter which will
have to assume how the mapped class will be aliased, but that feels
fragile and it'd be nice if I could let SQLAlchemy generate the column
names somehow. I get the feeling though that OVERLAPS is a bit
unusual in terms of the necessary support since it has non-scalar left
and right values for the operator.
Thanks for any help.
-- David
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
