On Jan 11, 2013, at 4:37 PM, Ryan Kelly wrote:
> I'm trying to figure out the correct way to use these array comparisons
> features specific to postgres, e.g.:
>
> select * from foo where 1 = any(bar);
>
> So I tried this:
>
> from sqlalchemy.sql.expression import func
> session.query(foo).filter(1 == func.any(foo.c.bar))
>
> But that didn't work, as I got this (essentially):
>
> select * from foo where any(bar) = 1;
reading this, it seemed extremely surprising that the = operator would suddenly
not be commutative. So I took a look at "ANY" in the PG docs, and I don't
really see this as the "=" operator anymore, it's a special PG operator that is
essentially "= ANY". So we can use op() for that. Then, to build this with
off-the-shelf pieces, we have to jump through the many hoops of this very
non-standard syntax:
from sqlalchemy.dialects import postgresql
from sqlalchemy import literal, tuple
from sqlalchemy.dialects.postgresql import array
literal(1).op("= ANY")(tuple_(array([foo.c.bar])))
then the NOT case (~ is equivalent to not_()):
expr = (~literal(1)).op("= ANY")(tuple_(array([foo.c.bar])))
seems to work:
print expr.compile(dialect=postgresql.dialect())
NOT %(param_1)s = ANY (ARRAY[foo.bar])
now that's cobbling it together with off the shelf pieces, which you could of
course throw into a function called any() to hide that boilerplate. But also,
we can make this into a construct where we can build up the string directly
(and this you can use to build anything without too much guessing):
from sqlalchemy.types import Boolean
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.compiler import OPERATORS
from sqlalchemy.sql.expression import ColumnElement, literal
class Any(ColumnElement):
type = Boolean()
def __init__(self, left, right, operator=operator.eq):
self.left = literal(left)
assert isinstance(right, array)
self.right = right
self.operator = operator
@compiles(Any, "postgresql")
def compile_any(element, compiler, **kw):
return "%s%sANY(%s)" % (
compiler.process(element.left),
OPERATORS[element.operator],
compiler.process(element.right)
)
print Any(1, array([foo.c.bar])).compile(dialect=postgresql.dialect())
# %(param_1)s = ANY(ARRAY[foo.bar])
print (~Any(1, array([foo.c.bar]))).compile(dialect=postgresql.dialect())
# NOT %(param_1)s = ANY(ARRAY[foo.bar])
These functions (ANY, ALL, etc.) should probably be provided as part of
sqlalchemy.dialects.postgresql to start with as well as be available as methods
from the ARRAY comparator.
--
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.
