ruby-on-rails - 带有嵌套表的 ActiveRecord 查询

Question

我正在尝试使用 ActiveRecord 进行下一次查询，但不知道如何在不使用 find_by_sql 调用的情况下获得此 SQL 结果。

SELECT *
FROM table_x AS a,
    (SELECT locator, max(day_parsed) AS max_day
     FROM table_x
     WHERE day_parsed BETWEEN params[:day_from] AND params[:day_to]  
     GROUP BY locator) AS b
WHERE a.locator = b.locator
AND a.day_parsed = b.max_day

有什么想法吗？

Answer 1

例如，我们有一个事件记录TableX，它的表名是table_x，所以查询将是:

from = params[:day_from]
to   = params[:day_to]

TableX.joins("JOIN (
                     SELECT locator, max(day_parsed) AS max_day
                     FROM table_x
                     WHERE day_parsed BETWEEN #{from} AND #{to}  
                     GROUP BY locator
                   ) as b ON b.locator = table_x.locator")
      .where("table_x.day_parsed = b.max_day")