如何使用rails ActiveRecord执行带有子查询的模型的左连接

Question

我使用的是rails 5。当我在rails控制台中执行以下直接SQL查询时，我得到了以下预期结果：

    2.3.5 :053 >   w = ActiveRecord::Base.connection.exec_query("SELECT * FROM students s LEFT JOIN (SELECT student_id, MAX(urgent) AS has_urgent,MAX(created_at) AS last_contact FROM reports GROUP BY student_id) r ON r.student_id = s.id")
   (0.5ms)  SELECT * FROM students s LEFT JOIN (SELECT student_id, MAX(urgent) AS has_urgent,MAX(created_at) AS last_contact FROM reports GROUP BY student_id) r ON r.student_id = s.id
 => #<ActiveRecord::Result:0x00000000051e1518 @columns=["id", "name", "cwid", "email", "phone", "company", "role", "advisor", "advisor_email", "mentor", "created_at", "updated_at", "student_id", "has_urgent", "last_contact"], @rows=[[1, "Johnny Smith", "71419940", "jsmith@gmail.com", "8435550001", "foxtrot", "mentor", "John I. Moore, Jr.", "john.moore@citadel.edu", "", "2017-11-13 14:58:50.128114", "2017-11-13 14:58:50.128168", nil, nil, nil], [2, "Shelly", "12345678", "shelly@gmail.com", "8435550002", "bravo", "mentee", "Michael P. Verdicchio", "mv@citadel.edu", "Johnny Smith", "2017-11-13 14:58:50.160195", "2017-11-13 14:58:50.160243", "2", "t", "2017-11-13 14:58:50.210105"], [3, "Max", "87654321", "max@gmail.com", "8435550003", "palmetto", "unassigned", "Mei-Qin Chen", "mei.chen@citadel.edu", "", "2017-11-13 14:58:50.179220", "2017-11-13 14:58:50.179258", nil, nil, nil], [4, "George", "87654325", "george@gmail.com", "8435550004", "palmetto", "mentee", "Deepti Joshi", "djoshi@citadel.edu", "Johnny Smith", "2017-11-13 14:58:50.189733", "2017-11-13 14:58:50.189762", "4", "f", "2017-11-03 14:58:50.260914"]], @hash_rows=nil, @column_types={}> 

但是，这将返回一个ActiveRecord：：结果类型，但我真正想要的是使用Rails ActiveRecord执行相同的查询，但返回一个ActiveRecord::Relation，我这样想如下：

2.3.5 :054 > w = Student.joins("LEFT JOIN (SELECT student_id, MAX(urgent) AS has_urgent,MAX(created_at) AS last_contact FROM reports GROUP BY student_id) r ON r.student_id = students.id")
  Student Load (0.5ms)  SELECT  "students".* FROM "students" LEFT JOIN (SELECT student_id, MAX(urgent) AS has_urgent,MAX(created_at) AS last_contact FROM reports GROUP BY student_id) r ON r.student_id = students.id LIMIT ?  [["LIMIT", 11]]
 => #<ActiveRecord::Relation [#<Student id: 1, name: "Johnny Smith", cwid: "71419940", email: "jsmith@gmail.com", phone: "8435550001", company: "foxtrot", role: "mentor", advisor: "John I. Moore, Jr.", advisor_email: "john.moore@citadel.edu", mentor: "", created_at: "2017-11-13 14:58:50", updated_at: "2017-11-13 14:58:50">, #<Student id: 2, name: "Shelly", cwid: "12345678", email: "shelly@gmail.com", phone: "8435550002", company: "bravo", role: "mentee", advisor: "Michael P. Verdicchio", advisor_email: "mv@citadel.edu", mentor: "Johnny Smith", created_at: "2017-11-13 14:58:50", updated_at: "2017-11-13 14:58:50">, #<Student id: 3, name: "Max", cwid: "87654321", email: "max@gmail.com", phone: "8435550003", company: "palmetto", role: "unassigned", advisor: "Mei-Qin Chen", advisor_email: "mei.chen@citadel.edu", mentor: "", created_at: "2017-11-13 14:58:50", updated_at: "2017-11-13 14:58:50">, #<Student id: 4, name: "George", cwid: "87654325", email: "george@gmail.com", phone: "8435550004", company: "palmetto", role: "mentee", advisor: "Deepti Joshi", advisor_email: "djoshi@citadel.edu", mentor: "Johnny Smith", created_at: "2017-11-13 14:58:50", updated_at: "2017-11-13 14:58:50">]> 

对于第二种方法，结果中不显示正确的表列，尽管生成的SQL看起来非常相似。我是Rails和ActiveRecord的新手，所以如果有人能帮助我理解为什么这两个结果是不同的，以及我应该如何使第二个ActiveRecord查询像直接的ActiveRecord查询一样工作，我会很感激。

Answer 1

如果您使用的是rails 5，那么使用left_joins or left_outer_joins。试试这个，如果对你有用的话，让我知道。

Student.left_outer_joins(:reports).select("student.*, MAX(urgent) AS has_urgent,MAX(created_at) AS last_contact").group("student.id")。

如果您想采用第一种方法，那么这些方法可能会对您有所帮助。

1. result.columns #获取结果的列名。
2. result.rows #获取结果的记录值。
3. result.to_hash #获取表示结果的散列数组(列=>值)
4. #ActiveRecord::Result also includes Enumerable。