This is something that could be improved in SQLAlchemy, but as a
workaround you can use the compiler extension to create the support
yourself. Here's some example code. It uses some private internals
from SQLAlchemy so you need to keep an eye on it that it doesn't break
when changing versions.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ClauseElement
from sqlalchemy.sql.expression import _literal_as_binds, _CompareMixin
from sqlalchemy.types import NullType
class TupleClause(ClauseElement, _CompareMixin):
def __init__(self, *columns):
self.columns = [_literal_as_binds(col) for col in columns]
self.type = NullType()
@compiles(TupleClause)
def compile_tupleclause(element, compiler, **kw):
return "(%s)" % ", ".join(compiler.process(col) for col in
element.columns)
# Usage:
def overlaps(a_pair, b_pair):
return TupleClause(*a_pair).op('OVERLAPS')(TupleClause(*b_pair))
query.filter(overlaps((MappedClass.start_col, MappedClass.end_col),
(start_time, end_time)))
On Aug 21, 10:50 am, David Bolen <db3l....@gmail.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
