Hello,
I am trying apply the order_by method on a query with a mapper that
uses eager loading.
mapper(User, users_table, properties = {
'addresses' : relation(Address, backref=backref('user',
lazy=False))
})
...
session.query(Address).order_by(asc(User.c.user_name)).select()
When doing the outer join an alias is applied to the users table. So
it is not able to map the column specified in the order_by attribute.
sqlalchemy.exceptions.SQLError: (OperationalError) no such column:
users.user_name u'SELECT ....
FROM addresses LEFT OUTER JOIN users AS anon_62c6
I saw the documentation:
Combining Eager Loads with Result Set Mappings
http://www.sqlalchemy.org/docs/adv_datamapping.html#advdatamapping_resultset_combining
But not sure I understand it.
Is it possible to use order_by without creating explicitly an outer
join, alias, and the option contains_eager?
Is there an option to avoid labeling the related tables with
anon_XXXX?
complete example below.
cheers,
Eduardo
---------
from sqlalchemy import *
metadata = MetaData()
# a table to store users
users_table = Table('users', metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(40)),
Column('password', String(80))
)
# a table that stores mailing addresses associated with a specific
user
addresses_table = Table('addresses', metadata,
Column('address_id', Integer, primary_key = True),
Column('user_id', Integer, ForeignKey("users.user_id")),
Column('street', String(100)),
Column('city', String(80)),
Column('state', String(2)),
Column('zip', String(10))
)
class User(object):
pass
class Address(object):
pass
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
'addresses' : relation(Address, backref=backref('user',
lazy=False))
})
# engine
engine = create_engine("sqlite://mydb.db")
metadata.create_all(engine)
# session
session = create_session(bind_to=engine)
### test ordering
engine.echo = True
# order by column on same table works fine.
session.query(Address).order_by(asc(Address.c.zip)).select()
# order by column on different table doesnt work.
session.query(Address).order_by(asc(User.c.user_name)).select()
--------------------
2007-07-11 01:33:06,683 INFO sqlalchemy.engine.base.Engine.0x..90
SELECT addresses.city AS addresses_city, addresses.address_id AS
addresses_address_id, addresses.user_id AS addresses_user_id,
addresses.zip AS addresses_zip, addresses.state AS addresses_state,
addresses.street AS addresses_street, anon_62c6.user_name AS
anon_62c6_user_name, anon_62c6.password AS anon_62c6_password,
anon_62c6.user_id AS anon_62c6_user_id
FROM addresses LEFT OUTER JOIN users AS anon_62c6 ON anon_62c6.user_id
= addresses.user_id ORDER BY addresses.zip ASC, anon_62c6.oid
2007-07-11 01:33:06,684 INFO sqlalchemy.engine.base.Engine.0x..90 []
2007-07-11 01:33:06,696 INFO sqlalchemy.engine.base.Engine.0x..90
SELECT addresses.city AS addresses_city, addresses.address_id AS
addresses_address_id, addresses.user_id AS addresses_user_id,
addresses.zip AS addresses_zip, addresses.state AS addresses_state,
addresses.street AS addresses_street, anon_62c6.user_name AS
anon_62c6_user_name, anon_62c6.password AS anon_62c6_password,
anon_62c6.user_id AS anon_62c6_user_id
FROM addresses LEFT OUTER JOIN users AS anon_62c6 ON anon_62c6.user_id
= addresses.user_id ORDER BY users.user_name ASC, anon_62c6.oid
2007-07-11 01:33:06,697 INFO sqlalchemy.engine.base.Engine.0x..90 []
2007-07-11 01:33:06,698 INFO sqlalchemy.engine.base.Engine.0x..90
ROLLBACK
Traceback (most recent call last):
File "testesa.py", line 45, in ?
session.query(Address).order_by(asc(User.c.user_name)).select()
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 319, in select
return self.select_whereclause(whereclause=arg, **kwargs)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 326, in select_whereclause
return self._select_statement(statement, params=params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 996, in _select_statement
return self.execute(statement, params=params, **kwargs)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py",
line 896, in execute
result = self.session.execute(self.mapper, clauseelement,
params=params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py",
line 183, in execute
return self.connection(mapper,
close_with_result=True).execute(clause, params, **kwargs)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 520, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 560, in execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams, **params)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 571, in execute_compiled
self._execute_raw(context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 584, in _execute_raw
self._execute(context)
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 602, in _execute
raise exceptions.SQLError(context.statement, context.parameters,
e)
sqlalchemy.exceptions.SQLError: (OperationalError) no such column:
users.user_name u'SELECT addresses.city AS addresses_city,
addresses.address_id AS addresses_address_id, addresses.user_id AS
addresses_user_id, addresses.zip AS addresses_zip, addresses.state AS
addresses_state, addresses.street AS addresses_street,
anon_62c6.user_name AS anon_62c6_user_name, anon_62c6.password AS
anon_62c6_password, anon_62c6.user_id AS anon_62c6_user_id \nFROM
addresses LEFT OUTER JOIN users AS anon_62c6 ON anon_62c6.user_id =
addresses.user_id ORDER BY users.user_name ASC, anon_62c6.oid' []
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
