正在更新ActiveRecord查询

Question

获取一个多部分标识符错误，但不确定如何重构它以执行SQL联接。我使用的是ruby 2.3.1和rails 4.1.6。这在SQL Server 2008上有效，但我需要更新它以使用SQL Server 2016，以便在底部查询中返回相同的信息。

原始代码为：

scope :ecr_production_review, includes(:qualification_records=>:task).active.where(
Task.table_name + '.training_area_id = ? AND ' + QualificationRecord.table_name + '.final_date is not null ',
'msvs 003'
)

产生错误：

ODBC::Error: 37000 (4104) [unixODBC][FreeTDS][SQL Server]The multi-part identifier "QualityDC.dbmaster.qualification_wrappers.final_date" could not be bound.: EXEC sp_executesql N'SELECT [QualityDC].[dbmaster].[employees].* FROM [QualityDC].[dbmaster].[employees] WHERE (QualityDC.dbmaster.Tasks.training_area_id = N''msvs 003'' AND QualityDC.dbmaster.qualification_wrappers.final_date is not null )'

我该如何更新ruby代码以生成这样的SQL？

Select e.id, e.first_name, e.last_name, t.training_area_id, qw.final_date FROM [QualityDC].[dbmaster].[employees] as e
JOIN QualityDC.dbmaster.emp_dept_levs as edl
ON edl.employee_id = e.id
JOIN QualityDC.dbmaster.qualification_wrappers as qw
ON qw.emp_dept_lev_id = edl.id
JOIn QualityDC.dbmaster.Tasks as t
ON t.id = qw.department_task_link_id
WHERE (t.training_area_id = 'msvs 003' AND qw.final_date is not null)

在服务器上生成：

SELECT [QualityDC].[dbmaster].[employees].[id] AS t0_r0, [QualityDC].[dbmaster].[employees].[id_number] AS t0_r1, [QualityDC].[dbmaster].[employees].[first_name] AS t0_r2, [QualityDC].[dbmaster].[employees].[last_name] AS t0_r3, [QualityDC].[dbmaster].[employees].[status] AS t0_r4, [QualityDC].[dbmaster].[employees].[hire_date] AS t0_r5, [QualityDC].[dbmaster].[employees].[created_at] AS t0_r6, [QualityDC].[dbmaster].[employees].[updated_at] AS t0_r7, [QualityDC].[dbmaster].[employees].[sap_id] AS t0_r8, [QualityDC].[dbmaster].[employees].[process_engineer] AS t0_r9, [QualityDC].[dbmaster].[employees].[birthday] AS t0_r10, [QualityDC].[dbmaster].[employees].[meeting_area_id] AS t0_r11, [QualityDC].[dbmaster].[employees].[email] AS t0_r12, [QualityDC].[dbmaster].[employees].[sendemail] AS t0_r13, [QualityDC].[dbmaster].[employees].[number3M] AS t0_r14, [QualityDC].[dbmaster].[employees].[username] AS t0_r15, [QualityDC].[dbmaster].[qualification_wrappers].[id] AS t1_r0, [QualityDC].[dbmaster].[qualification_wrappers].[supervisor_id] AS t1_r1, [QualityDC].[dbmaster].[qualification_wrappers].[process_engineer_id] AS t1_r2, [QualityDC].[dbmaster].[qualification_wrappers].[emp_dept_lev_id] AS t1_r3, [QualityDC].[dbmaster].[qualification_wrappers].[department_task_link_id] AS t1_r4, [QualityDC].[dbmaster].[qualification_wrappers].[created_at] AS t1_r5, [QualityDC].[dbmaster].[qualification_wrappers].[updated_at] AS t1_r6, [QualityDC].[dbmaster].[qualification_wrappers].[final_date] AS t1_r7, [QualityDC].[dbmaster].[Tasks].[id] AS t2_r0, [QualityDC].[dbmaster].[Tasks].[training_area_id] AS t2_r1, [QualityDC].[dbmaster].[Tasks].[training_area] AS t2_r2, [QualityDC].[dbmaster].[Tasks].[req_no] AS t2_r3, [QualityDC].[dbmaster].[Tasks].[pe_signoff] AS t2_r4, [QualityDC].[dbmaster].[Tasks].[obsolete] AS t2_r5, [QualityDC].[dbmaster].[Tasks].[created_at] AS t2_r6, [QualityDC].[dbmaster].[Tasks].[updated_at] AS t2_r7, [QualityDC].[dbmaster].[Tasks].[classification_id] AS t2_r8, [QualityDC].[dbmaster].[Tasks].[pieces] AS t2_r9 FROM [QualityDC].[dbmaster].[employees] LEFT OUTER JOIN [QualityDC].[dbmaster].[emp_dept_levs] ON [QualityDC].[dbmaster].[emp_dept_levs].[employee_id] = [QualityDC].[dbmaster].[employees].[id] LEFT OUTER JOIN [QualityDC].[dbmaster].[qualification_wrappers] ON [QualityDC].[dbmaster].[qualification_wrappers].[emp_dept_lev_id] = [QualityDC].[dbmaster].[emp_dept_levs].[id] LEFT OUTER JOIN [QualityDC].[dbmaster].[department_task_links] ON [QualityDC].[dbmaster].[department_task_links].[id] = [QualityDC].[dbmaster].[qualification_wrappers].[department_task_link_id] LEFT OUTER JOIN [QualityDC].[dbmaster].[Tasks] ON [QualityDC].[dbmaster].[Tasks].[id] = [QualityDC].[dbmaster].[qualification_wrappers].department_task_link_id WHERE ([QualityDC].[dbmaster].[Tasks].[training_area_id] = 'msvs 001' AND [QualityDC].[dbmaster].[qualification_wrappers].[final_date] IS NOT NULL)

Answer 1

用enginesmnky的块解决：

includes(qualification_records: :task).references(qualification_records: :task).where(Task.arel_table[:training_area_id].eq('msvs 003').and(QualificationRecord.arel_table[:final_date].not_eq(nil)))