On Aug 13, 2012, at 3:25 PM, Vlad K. wrote: > On 08/13/2012 08:53 PM, Michael Bayer wrote: >> we don't currently have native support for ARRAY operations and there is >> some infrastructure work that should make it easier in 0.8 to add. >> >> for now, when you need custom operators use op(): >> >> Model.array_column.op("@>")("somevalue") > > I tried that, but that doesn't work because the second operand has to be > wrapped in ARRAY[], afaik that's the only way to lookup a value in an array > column (which is also gist indexable). > > > If I do > > Model.array_column.op("@>")('ARRAY[123]') > > I get > > SELECT * FROM model_table WHERE array_column @> 'ARRAY[123]' > > > and I need ARRAY[123] without quotes.
we can keep turning the crank here, here's a full series of examples to make this happen: # step 1. build a custom element to do the "ARRAY[xyz]" part of this. from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql import ColumnElement from sqlalchemy.sql.expression import _literal_as_binds from sqlalchemy.dialects.postgresql import ARRAY class MyArray(ColumnElement): type = ARRAY def __init__(self, expr): self.expr = _literal_as_binds(expr) @compiles(MyArray) def compile(element, compiler, **kw): return "ARRAY[%s]" % compiler.process(element.expr) # step 2. illustrate raw usage of MyArray with the ORM. from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String Base = declarative_base() class MyClass(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) arrvalue = Column(ARRAY(String)) print MyClass.arrvalue.op("@>")(MyArray("123")) # step 3. use ColumnProperty to define a "comparator" at the # ORM level. from sqlalchemy.orm.properties import ColumnProperty class MyComparator(ColumnProperty.Comparator): def array_in(self, other): return self.__clause_element__().op("@>")(MyArray(other)) class MyOtherClass(Base): __tablename__ = 'bar' id = Column(Integer, primary_key=True) arrvalue = ColumnProperty(Column(ARRAY(String)), comparator_factory=MyComparator) print MyOtherClass.arrvalue.array_in("123") # step 4. 0.8 will have a "core" method for the "comparison" part which is easier. -- 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.