Many-to-many relationships are ignored by select()

[Former user]

The defect is illustrated by running the following code:

from sqlalchemy import *
engine = create_engine('sqlite://mydb.db', echo=True)

users = Table('users', engine,
    Column('id', Integer, primary_key=True),
    Column('user_name', String(16)),
)

addresses = Table('addresses', engine,
    Column('id', Integer, primary_key=True),
    Column('address', String(100)),
)

useraddresses = Table('useraddresses', engine,
    Column('id', Integer, primary_key=True),
    Column('userid', Integer, ForeignKey('users.id')),
    Column('addressid', Integer, ForeignKey('addresses.id')),
)

class User(object): pass

class Address(object): pass

assign_mapper(User,users)
assign_mapper(Address,addresses)

users.create()
addresses.create()
useraddresses.create()

User.select(Address.c.address=='foobar')

The SQL generated by the last line is:

SELECT users.user_name AS users_user_name, users.id AS users_id
FROM addresses, users
WHERE addresses.address = ?

This statement will select far more rows than necessary. I suppose that the mapper will filter out these rows, but if the addresses table is large that will be a very long process (as I found out the hard way).

[Former user]

This ticket submitted by mrshoe.

[Mike Bayer]

the correct call is:

User.select(and_(Address.c.address=='foobar', Address.c.user_id==User.c.user_id))

the select function does not automatically locate relations upon the class with which to automatically construct join conditions. select provides an explicit interface, not an implicit one. there are in fact many ambiguities present in the idea of constructing join conditions automatically; for an example, see http://www.mail-archive.com/[e-mail]/msg01684.html .

Some "automatic join" creation functionality is available using the "select_by" statement:

User.select_by(address='foobar')

as this specifies comparisons based on keynames and not columns, the keynames are corresponded to the properties placed off the mapper, and therefore is much less ambiguous.

its looking like a more comprehensive select_by function, which takes into account things like ORDER_BY and LIMIT is sorely needed.

[Mike Bayer]

oh also, you arent including the "usersaddresses" table in your mapper setup:

assign_mapper(User,users)
assign_mapper(Address,addresses, secondary=usersaddresses)

the full select join is then:

l = mapper.select(and_(users.c.user_id==usersaddresses.c.user_id, usersaddresses.c.user_id==addresses.c.user_id, addresses.c.address=='foobar'))

Again, select_by is a lot easier. I am also posting to the list some more information about select_by which you may find useful.