Hi,
With SQLAlchemy 0.3.8, I wanted to select from a many-to-many
relationship with ORM, and found that if the select condition contains
fields in the relation table, the relation table will be duplicated in
the select statement and thus leading to wrong results.
In the following example, I am trying to find all person who lives at
street 'mystreet':
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')),
Column('address_id', Integer, ForeignKey('address.address_id'))
)
class Person(object): pass
class Address(object): pass
mapper(Address, address)
personmapper = mapper(Person, person,
properties={'addresses': relation(Address,
secondary=person_address, lazy=False)}
)
persons = session.query(Person).select(address.c.street=='mystreet')
Running the above generates the following SQL statement:
2007-06-22 23:57:08,660 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT person.person_id AS person_person_id, person.person_nam
e AS person_person_name, anon_1ee3.address_id AS anon_1ee3_address_id,
anon_1ee3.street AS anon_1ee3_street
FROM address, person LEFT OUTER JOIN person_address AS anon_3f1c ON
person.person_id = anon_3f1c.person_id LEFT OUTER JOIN addre
ss AS anon_1ee3 ON anon_1ee3.address_id = anon_3f1c.address_id
WHERE address.street = ? ORDER BY person.oid, anon_3f1c.oid
2007-06-22 23:57:08,707 INFO sqlalchemy.engine.base.Engine.0x..d0
['mystreet']
The table 'address' appears first in the FROM clause and then again in
the JOIN clause. This doesn't seem to be the right select statement.
Is this a bug? If no, how should I structure my code to eliminate this
duplication? If yes, is there a work-around?
Thanks,
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 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
-~----------~----~----~----~------~----~------~--~---
