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#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructing thequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN On Mar 22, 2007, at 9:44 PM, HD Mail wrote: > > Hi, > > Sorry for the long email, below is a dump of the SA generated sql. > I have a relationship in one of my mappers to an abitrary select > (task_status). > My mapper also orders the result by a column in this abitrary select. > > I am using the latest SA from trunk. This use to work in an earlier > version. > > The sql is generated from the following mapping > > task_status = select( > [sys_enum.c.id, > sys_enum.c.key_code.label('code'), > sys_enum.c.value.label('description'), > sys_enum.c.seq_no], > sys_enum.c.type_code=='status' > ).alias('status') > > db.mapper(Task, db.task, > properties = { > 'created_user': relation(Employee, lazy=False, > primaryjoin=db.task.c.created_by==db.employee.c.employee_id), > 'assigned_user': relation(Employee, lazy=False, > primaryjoin=db.task.c.assigned_to==db.employee.c.employee_id), > 'project': relation(Project, lazy=False), > 'status': relation(status_mapper, lazy=False, > primaryjoin=and_ > (task_status.c.code==db.task.c.status_code), > foreignkey=db.task.c.status_code > ), > 'task_details': relation(TaskDetail, lazy=True, cascade='all, > delete-orphan'), > }, > order_by = [db.task.c.project_id, desc(task_status.c.seq_no), > db.task.c.started_on, db.tas > k.c.finished_on, db.task.c.sort_no, db.task.c.priority] > ) > > Error(SQLError): (ProgrammingError) relation "status" does not exist > 'SELECT anon_824a.code AS anon_824a_code, anon_824a.seq_no AS > anon_824a_seq_no, anon_824a.id AS anon_824a_id, > anon_824a.description AS > anon_824a_description, task.project_id AS task_project_id, > task.description AS task_description, task.task_id AS task_task_id, > task.status_code AS task_status_code, task.created_on AS > task_created_on, task.created_by AS task_created_by, task.sort_no AS > task_sort_no, project_6f4a.project_name AS project_6f4a_project_name, > project_6f4a.chargeable AS project_6f4a_chargeable, > project_6f4a.enabled > AS project_6f4a_enabled, project_6f4a.parent_id AS > project_6f4a_parent_id, project_6f4a.rate AS project_6f4a_rate, > client_3f15.phone_area_code AS client_3f15_phone_area_code, > client_3f15.fax_no AS client_3f15_fax_no, client_3f15.address1 AS > client_3f15_address1, client_3f15.client_name AS > client_3f15_client_name, client_3f15.address3 AS client_3f15_address3, > client_3f15.fax_area_code AS client_3f15_fax_area_code, > client_3f15.abn > AS client_3f15_abn, client_3f15.phone_no AS client_3f15_phone_no, > client_3f15.post_code AS client_3f15_post_code, > client_3f15.client_id AS > client_3f15_client_id, client_3f15.country_name AS > client_3f15_country_name, client_3f15.state_code AS > client_3f15_state_code, client_3f15.address2 AS client_3f15_address2, > client_3f15.enabled AS client_3f15_enabled, project_6f4a.client_id AS > project_6f4a_client_id, project_6f4a.project_id AS > project_6f4a_project_id, project_6f4a.project_code AS > project_6f4a_project_code, task.finished_on AS task_finished_on, > employee_6c57.first_name AS employee_6c57_first_name, > employee_6c57.employee_id AS employee_6c57_employee_id, > employee_6c57.middle_name AS employee_6c57_middle_name, > employee_6c57.employee_code AS employee_6c57_employee_code, > employee_6c57.home_phone_no AS employee_6c57_home_phone_no, > employee_6c57.tfn AS employee_6c57_tfn, employee_6c57.mobile_no AS > employee_6c57_mobile_no, employee_6c57.client_id AS > employee_6c57_client_id, employee_6c57.surname AS > employee_6c57_surname, > employee_6c57.home_area_code AS employee_6c57_home_area_code, > employee_6c57.password AS employee_6c57_password, task.assigned_to AS > task_assigned_to, task.due_on AS task_due_on, task.estimate AS > task_estimate, employee_ba6b.first_name AS employee_ba6b_first_name, > employee_ba6b.employee_id AS employee_ba6b_employee_id, > employee_ba6b.middle_name AS employee_ba6b_middle_name, > employee_ba6b.employee_code AS employee_ba6b_employee_code, > employee_ba6b.home_phone_no AS employee_ba6b_home_phone_no, > employee_ba6b.tfn AS employee_ba6b_tfn, employee_ba6b.mobile_no AS > employee_ba6b_mobile_no, employee_ba6b.client_id AS > employee_ba6b_client_id, employee_ba6b.surname AS > employee_ba6b_surname, > employee_ba6b.home_area_code AS employee_ba6b_home_area_code, > employee_ba6b.password AS employee_ba6b_password, task.resolution AS > task_resolution, task.started_on AS task_started_on, task.category AS > task_category, task.priority AS task_priority \nFROM task LEFT OUTER > JOIN (SELECT sys_enum.id AS id, sys_enum.key_code AS code, > sys_enum.value AS description, sys_enum.seq_no AS seq_no \nFROM > sys_enum > \nWHERE sys_enum.type_code = %(sys_enum_type_code)s) AS anon_824a ON > anon_824a.code = task.status_code LEFT OUTER JOIN project AS > project_6f4a ON project_6f4a.project_id = task.project_id LEFT OUTER > JOIN client AS client_3f15 ON client_3f15.client_id = > project_6f4a.client_id LEFT OUTER JOIN employee AS employee_6c57 ON > task.created_by = employee_6c57.employee_id LEFT OUTER JOIN > employee AS > employee_ba6b ON task.assigned_to = employee_ba6b.employee_id \nWHERE > task.assigned_to = %(task_assigned_to)s AND (task.finished_on IS > NULL OR > CAST(task.finished_on AS DATE) > %(literal)s) ORDER BY > task.project_id, > status.seq_no DESC, task.started_on, task.finished_on, task.sort_no, > task.priority, anon_824a.id, project_6f4a.project_id, > client_3f15.client_id, employee_6c57.employee_id, > employee_ba6b.employee_id' {'literal': datetime.date(2007, 3, 18), > 'sys_enum_type_code': 'status', 'task_assigned_to': 2} > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
