Many-to-many relationships are ignored by select()
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).
Comments (3)
-
Account Deleted -
repo owner - changed status to invalid
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/sqlalchemy-users@lists.sourceforge.net/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. -
repo owner 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.
- Log in to comment
This ticket submitted by mrshoe.