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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
