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)
