put the order_by in your "status" relation. On Mar 26, 2007, at 12:23 PM, HD Mail wrote:
> > >> I think the issue is you cant put a "task_status" ordering in your >> Task mapper since that table is not part of its mapping. >> >> http://www.sqlalchemy.org/trac/wiki/ >> FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstruct >> ing >> thequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOI >> N >> >> > I'm a bit confused. So with a mapping as follows. > > db.mapper(TaskStatus, db.sys_task_status) > db.mapper(Task, db.task, > properties = { > 'status': relation(TaskStatus, lazy=False), > } > ) > > Is the only way for me to order by a column in sys_task_status is with > an explicit join like in this example ? > > query = db.query(model.Task).select_from( > db.task.join(db.sys_task_status) > ).order_by(db.sys_task_status.c.seq_no) > > which results in the following SQL: > > SELECT sys_task_status_cf27.*, task.* > FROM task JOIN sys_task_status ON sys_task_status.status_code = > task.status_code > LEFT OUTER JOIN sys_task_status AS sys_task_status_cf27 ON > sys_task_status_cf27.status_code = task.status_code > ORDER BY sys_task_status.seq_no, sys_task_status_cf27.status_code > > I'm trying to get to the following query. It takes half the time of > the > first query. > > SELECT sys_task_status_cf27.*, task.* > FROM task LEFT OUTER JOIN sys_task_status AS sys_task_status_cf27 ON > sys_task_status_cf27.status_code = task.status_code > ORDER BY sys_task_status_cf27.seq_no, sys_task_status_cf27.status_code > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---