eager loads are aliased to prevent them from interfering with your
normal query criterion, which may include joins to the same tables
which are being eagerly loaded.
similarly, the eager load queries, when ordered, are usually intended
to control the ordering of the *child items*, not the parent items.
for example you may wish to query Users, sorting by Address in
reverse order; for this youd join from User to Address and sort by
Address id. *but*, each User object would still have its Address
members populated in the order specified by the "addresses" relation,
since the eager load is aliased and has its own "order by".
So the usual way to order by something is to just join it and order
explicitly:
session.query(Address).join('user').order_by(asc
(User.c.user_name)).list()
that way you neednt worry about eager or lazy loading (which is how
they were meant to be used).
On Jul 10, 2007, at 4:06 PM, schettino72 wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
