I see two things that are problematic here. one potential problem, but is probably OK in this example, is that you have a "vet" property defined on both Cat and Dog and they are both set up to eagerly load using a join. this might create problems if you try to query for _Animal objects polymorphically, but if you query just for Cat or Dog individually it should be OK. the other issue is that your queries at the bottom are filtering on a related table but are not setting up a join condition between the primary and secondary table. you probably want to say:
print db.Query(db.Cat).join('vet').filter(db.CatVet.kennel == True).all() print db.Query(db.Cat).join('vet').filter(db.CatVet.kennel == False).all() if problems persist, turn on SQL echoing (using echo='debug' will show result sets as well), then step through the example and first ensure that the flush() populates the DB correctly, then take a look at the SQL issued by the queries to better understand what might be needed. On Dec 29, 2007, at 6:08 AM, Dave Harrison wrote: > > Hi all, > > I've got a situation where I want to use inherited mappers, with joins > to other inherited mappers. However I'm getting some weird results > where the following query should show no cat objects, instead I get 2 > back for both queries. > > Can anyone spot the flaw in my code here ? > > The api and test script are transcribed below. > > Cheers > D > > --- > > testapi.py > ==== > > #!/usr/local/bin/python2.5 > > from sqlalchemy import * > from sqlalchemy.orm import * > > session = scoped_session( > sessionmaker(autoflush=False, transactional=True) > ) > mapper = session.mapper > metadata = MetaData() > > ANIMAL_TYPE_DOG = 1 > ANIMAL_TYPE_CAT = 2 > > animalTable = Table( > 'animal', > metadata, > Column('id', Integer, primary_key=True), > Column('type', Integer, nullable=False), > Column('name', String(100), nullable=False), > Column('vet_id', Integer, ForeignKey('vet.id')), > ) > > vetTable = Table( > 'vet', > metadata, > Column('id', Integer, primary_key=True), > Column('type', Integer, nullable=False), > Column('address', String(100), nullable=False), > Column('kennel', Boolean, nullable=False), > ) > > > class _Animal(object): > def printName(self): > print self.name > > class Cat(_Animal): > pass > > class Dog(_Animal): > pass > > > class _Vet(object): > def printAddress(self): > print self.address > > class CatVet(_Vet): > pass > > class DogVet(_Vet): > pass > > > animalMapper = mapper( > _Animal, > animalTable, > polymorphic_on=animalTable.c.type, > ) > mapper( > Dog, > inherits=animalMapper, > polymorphic_identity=ANIMAL_TYPE_DOG, > properties = { > "vet": relation( > DogVet, > lazy=False, > ) > } > ) > mapper( > Cat, > inherits=animalMapper, > polymorphic_identity=ANIMAL_TYPE_CAT, > properties = { > "vet": relation( > CatVet, > lazy=False, > ) > } > ) > > vetMapper = mapper( > _Vet, > vetTable, > polymorphic_on=vetTable.c.type, > ) > mapper( > DogVet, > inherits=vetMapper, > polymorphic_identity=ANIMAL_TYPE_DOG, > ) > mapper( > CatVet, > inherits=vetMapper, > polymorphic_identity=ANIMAL_TYPE_CAT, > ) > > > def connect(uri): > engine = create_engine(uri, strategy="threadlocal") > metadata.bind = engine > return engine > > > def create(): > metadata.create_all() > > > def drop(): > metadata.drop_all() > > ==== > > test.py > > ==== > > #!/usr/local/bin/python2.5 > > import testapi as db > > DB = "sqlite:///test.db" > db.connect(DB) > > db.create() > > v1 = db.DogVet() > v1.address = "12 Foo St" > v1.kennel = True > v2 = db.CatVet() > v2.address = "29 Bar Rd" > v2.kennel = False > v3 = db.CatVet() > v3.address = "29 Bar Rd" > v3.kennel = True > > c1 = db.Cat() > c1.name = "muffin" > c1.vet = v2 > c2 = db.Cat() > c2.name = "bagel" > c2.vet = v2 > > d1 = db.Dog() > d1.name = "rex" > d1.vet = v1 > d2 = db.Dog() > d2.name = "bill" > d2.vet = v1 > > db.session.flush() > > print db.Query(db.Cat).filter(db.CatVet.kennel == True).all() > > print db.Query(db.Cat).filter(db.CatVet.kennel == False).all() > > db.drop() > db.session.flush() > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---