at its most simplistic level, IN is just a bunch of "X=Y" joined together via OR. Can that approach work here ? (seems like it would be the best way considering its polymorphic scalar elements being compared). At a higher level, seems like you'd want to group elements of common types together, and produce a list of the form (type1 IN (list of type 1s) OR type2 IN (list of type 2s) ....).
On May 27, 2008, at 2:25 PM, Martin Pengelly-Phillips wrote: > > Hi there, > > Just came back to some code after a bit of a break and can't seem to > get my head around how to correctly use the in_ method with a custom > comparator and also the orm subquery functionality. > > See attached file for an outline of my approach. > > You will see that I am stuck on how to correctly form the last query: > Roughly: select all entities with property (key='association' and > value IN (subquery)) > > It looks like I need to define an in_() method on my custom > comparator and then possibly use the orm subquery() method for the > er...subquery. > > It all feels like it should be straight forward, but the gears just > aren't turning at the moment so any pointers would be greatly > appreciated. > > > ta, > > > Martin > > > > > > import sys > from datetime import datetime > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.orm.interfaces import * > > # Database > engine = create_engine('sqlite:///') > metadata = MetaData() > Session = scoped_session(sessionmaker(autoflush=True, > transactional=False, bind=engine)) > mapper = Session.mapper > > > # Class: Entity > #---------------------------------------------- > class Entity(object): > > def __init__(self, properties=None): > self.properties = [] > if properties is not None: > self.properties.extend(properties) > > # Table: entities > #---------------------------------------------- > entityTable = Table('entities', metadata, > Column('id', Integer, primary_key=True), > Column('_entityType', String(30), nullable=False), > ) > > # Table: entitiesPolymorphicProperties > #---------------------------------------------- > entityPolymorphicPropertyTable = > Table('entitiesPolymorphicProperties', metadata, > Column('entity_id', Integer, > ForeignKey('entities.id')), > Column('polymorphicProperty_id', Integer, > ForeignKey('polymorphicProperties.id')), > ) > > # Class: PolymorphicProperty > #---------------------------------------------- > class PolymorphicProperty(object): > > typeMap = { > str: ('text','text'), > int: ('integer','integer'), > float: ('float','float'), > datetime: ('datetime','datetime'), > Entity: ('entity','entity'), > type(None): (None, None) > } > > def __init__(self, key=None, value=None): > self.key = key > self.value = value > > def _getValue(self): > for discriminator, field in self.typeMap.values(): > if self.type == discriminator: > return getattr(self, field) > return None > > def _setValue(self, value): > # Check type of value can be handled > pyType = type(value) > if pyType not in self.typeMap: > if isinstance(value, Entity): > pyType = Entity > else: > raise TypeError(pyType) > > # Set real values and current type of property > # Resets all real values no longer matching type to None > for fieldType in self.typeMap: > discriminator, field = self.typeMap[fieldType] > fieldValue = None > if pyType == fieldType: > self.type = discriminator > fieldValue = value > if field is not None: > setattr(self, field, fieldValue) > > def _delValue(self, value): > self._set_value(None) > > value = property(_getValue, _setValue, _delValue, doc='''Logical > value of this property''') > > > # Class: Comparator > #---------------------------------------------- > class Comparator(PropComparator): > > castableMap = { > int: (Integer, Text, Float), > str: (Text,), > float: (Float, Text), > datetime: (DateTime, Text), > type(None): (), > } > > castTypeMap = { > int: Integer, > str: Text, > float: Float, > datetime: DateTime, > } > > # Function: _case > # Generate CASE (switch) statement in SQL to pick > appropriate field > #---------------------------------------------- > def _case(self, castType=Text): > cls = self.prop.parent.class_ > whens = [] > for fieldType in cls.typeMap: > if fieldType in self.castableMap: > if castType in self.castableMap[fieldType]: > prop = cls.typeMap[fieldType] > if prop[1] is not None: > whens.append(( text("'%s'" % prop[0]), > cast(getattr(cls, prop[1]), castType) )) > return case(whens, cls.type, null()) > > # Function: __eq__ > # Equality comparison operator. > #---------------------------------------------- > def __eq__(self, other): > if isinstance(other, Entity): > return self.prop.parent.class_.entity == other > return self._case() == cast(other, Text) > > # Function: __ne__ > # In-equality comparison operator. > #---------------------------------------------- > def __ne__(self, other): > if isinstance(other, Entity): > return self.prop.parent.class_.entity != other > return self._case() != cast(other, Text) > > > # Table: polymorphicProperties > #---------------------------------------------- > polymorphicPropertyTable = Table('polymorphicProperties', metadata, > Column('id', Integer, primary_key=True), > Column('key', String(20), nullable=False, > default='keyword'), > Column('type', String(15), default=None), > Column('text', Text, default=None), > Column('integer', Integer, default=None), > Column('float', Float, default=None), > Column('datetime', DateTime, default=None), > > Column('entity_id', Integer, > ForeignKey('entities.id')), > ) > > # Mapping: PolymorphicProperty > #---------------------------------------------- > mapper(PolymorphicProperty, > polymorphicPropertyTable, > properties={ > 'value': > comparable_property(PolymorphicProperty.Comparator, > PolymorphicProperty.value), > 'entity': relation(Entity), > } > ) > > # Mapping: Entity > #---------------------------------------------- > mapper(Entity, entityTable, > polymorphic_on=entityTable.c._entityType, > polymorphic_identity='Entity', > properties={ > 'properties': relation(PolymorphicProperty, > secondary=entityPolymorphicPropertyTable, backref='entities'), > } > ) > > metadata.create_all(engine) > > # Main > #---------------------------------------------- > def main(): > > session = Session() > session.begin() > > prop1 = PolymorphicProperty(key='username', value='bob') > entity1 = Entity(properties=[prop1]) > > prop2 = PolymorphicProperty(key='resolution', value=5) > prop3 = PolymorphicProperty(key='association', value=entity1) > entity2 = Entity(properties=[prop2, prop3]) > > session.commit() > > # Entities with property (key=username, value='bob') > print > session.query(Entity).filter(Entity.properties.any(key='username', > value='bob')).all() > > # Entities with property (key=resolution, value=5) > print > session.query(Entity).filter(Entity.properties.any(key='resolution', > value=5)).all() > > # Entities with property (key=association, value=entity1) > print > session > .query(Entity).filter(Entity.properties.any(key='association', > value=entity1)).all() > > # Entities with property (key=association, value IN > [subquery(Entity).filter(Entity.properties.any(key='username', > value='bob'))]) > entities = > session > .query > (Entity > ).filter(Entity.properties.any(PolymorphicProperty.key=='asset', > > PolymorphicProperty > .value > .in_ > (session.query(Entity).filter(Entity.properties.any(key='username', > value='bob'))))).all() > > Session.remove() > return 0 > > # Driver > #---------------------------------------------- > if __name__ == '__main__': > sys.exit(main()) > --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---