Hi Michael,
Thank you for the quick response. I had thought about using a
straightforward OR statement - are you suggesting that this would form
the body of the in_() method on the Comparator or were you referring
more to just compiling the OR statements in the base query?
Also, what is the correct expression for defining a subquery with the
ORM interface (I gather my embedded session.query statement currently
evaluates as a separate statement to return the list of candidates).
ta,
Martin
On May 27, 7:33 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
