Having read the relavant section in the docs on this topic and made
some experimentations, I still have no clue how I can achieve the
following task:
Suppose we have a many-to-many relationship between the table users and address:
users = Table('users', engine,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(40)))
addresses = Table('addresses', engine,
Column('address_id', Integer, primary_key = True),
Column('street', String(100)))
user_address = Table('user_address', engine,
Column('user_id', Integer, ForeignKey('users.user_id'), primary_key=True),
Column('address_id', Integer, ForeignKey('addresses.address_id'),
primary_key=True))
class User(object): pass
Now I'd like to select all users with address_id x and map them to
class User. I attemped to build a select like this:
s = select([users], users.c.user_id==user_address.c.user_id)
and then define a mapper like in the example in documentation:
m = mapper(User, s)
This causes an error:
ArgumentError: Mapping against a Select object requires that it has a
name. Use an alias to give it a name, i.e. s =
select(...).alias('myselect')
Apparently the syntax in the example doesn't work as is. I added an
alias to the select:
m = mapper(User, s.alias('myselect'))
But I still can't use the mapper to generate the SQL code I expected.
The SQL generated for
m.select_by(user_address.c.address_id == 1)
is
'SELECT myselect.user_name AS myselect_user_name, myselect.user_id AS
myselect_user_id \nFROM user_address, (SELECT users.user_id AS
user_id, users.user_name AS user_name \nFROM users \nWHERE
users.user_id = user_address.user_id) AS myselect \nWHERE
user_address.address_id = %(user_address_address_id)s'
{'user_address_address_id': 1}
whereas I would expect the SQL to be something like:
SELECT users.user_name, users.user_id FROM users, user_address WEHRE
users.user_id = user_address.user_id and user_address.address_id = 1
What's the correct way to do this in SA?
Thanks.
--
Hong Yuan
大管家网上建材超市
装修装潢建材一站式购物
http://www.homemaster.cn
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
