On Jun 22, 12:11 pm, Hong Yuan <[EMAIL PROTECTED]> wrote:
> 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']
its correct. the LEFT OUTER JOINs above are the result of the
lazy=False eager load you have switched on...if you set lazy=True, you
will see the actual statement you are creating via your Query.
Because the OUTER JOIN tables are aliased, they have no bearing upon
the entity rows (that is, person rows), that are selected. to join
to the "address" table for the purposes of limiting "person" rows
based on "address", you have to tell Query what you want to join to:
persons =
session.query(Person).join('addresses').select(address.c.street=='mystreet')
try it first without the eager load to see what you get, then turn on
eager loading so you can see the difference.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
