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 [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
-~----------~----~----~----~------~----~------~--~---
