Hi,
I just find that if I introduct an association object for the many-to-
many relationship, then the selection seems problematic.
With the above, I added an PersonAddress class as the association
class, then selects against the address table columns. The select
statement produced this time, with eager-loading turned off, is:
2007-06-25 10:50:06,480 INFO sqlalchemy.engine.base.Engine.0x..b0
SELECT person.person_id AS person_person_id, person.person_nam
e AS person_person_name
FROM person JOIN person_address ON person.person_id =
person_address.person_id, address
WHERE address.street like ? ORDER BY person.oid
2007-06-25 10:50:06,528 INFO sqlalchemy.engine.base.Engine.0x..b0
['Street%']
There is one less JOIN condition. Instead, person is joined with
person_address, then a cartesian product is produced with address.
Although the ORM finally removes duplicate Person objects, the select
statement will returns too many duplicate rows and is thus dangerous
in production.
How can I put the second JOIN condition back into the generated
SELECT?
The test script is attached below.
from sqlalchemy import *
engine = create_engine('sqlite:///')
engine.echo=True
meta = BoundMetaData(engine)
session = create_session()
person = Table('person', meta,
Column('person_id', Integer, primary_key=True),
Column('person_name', String(30))
)
address = Table('address', meta,
Column('address_id', Integer, primary_key=True),
Column('street', String(30))
)
person_address = Table('person_address', meta,
Column('person_id', Integer, ForeignKey('person.person_id'),
primary_key=True),
Column('address_id', Integer, ForeignKey('address.address_id'),
primary_key=True),
Column('is_primary', Boolean, default=False)
)
meta.create_all()
class Person(object):
def __init__(self, id, name):
self.person_id = id
self.person_name = name
class Address(object):
def __init__(self, id, street):
self.address_id = id
self.street = street
class PersonAddress(object):
def __init__(self, address, is_primary=False):
self.address = address
self.is_primary = is_primary
mapper(Address, address)
person_address_mapper = mapper(PersonAddress, person_address,
properties={'address': relation(Address, lazy=False)}
)
personmapper = mapper(Person, person,
properties={
'addresses': relation(person_address_mapper,
association=PersonAddress,
lazy=True)
})
p1 = Person(1, 'me')
p2 = Person(2, 'you')
a1 = Address(1, 'Street One')
a2 = Address(2, 'Street Two')
p1.addresses.append(PersonAddress(a1, is_primary=True))
p1.addresses.append(PersonAddress(a2))
p2.addresses.append(PersonAddress(a1, is_primary=True))
p2.addresses.append(PersonAddress(a2))
session.save(p1)
session.save(p2)
session.flush()
persons =
session.query(Person).join('addresses').select(address.c.street.op('like')
('Street%'))
print len(persons)
Best Regards
Hong Yuan
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
