I am trying to prepare an ActiveRecord query combining data from 3 models. There are MachineGroups that have many Machines, which in turn can have many Outputs. I want to have a query that selects all machines from given machine group with outputs from within a given time range, which means that if a machine has zero outputs during given time range - should be included in the list, but with no output data.
我正在尝试准备一个组合来自3个模型的数据的ActiveRecord查询。有些机器组有许多机器，而这些机器又可以有许多输出。我想要一个查询，从给定的机器组中选择具有给定时间范围内输出的所有机器，这意味着如果机器在给定时间范围内的输出为零 - 应该包含在列表中，但没有输出数据。
SQL code:
SQL代码：
SELECT * FROM machines LEFT OUTER JOIN
(SELECT * FROM outputs
WHERE outputs.created_at >= "2017-07-25 05:00:00"
AND outputs.created_at < "2017-07-26 17:00:00") AS o
ON machines.id = o.machine_id
WHERE machines.machine_group_id = 1;
1 个解决方案
#1
1
Should be something like
应该是这样的
SELECT m.*
FROM machines m
LEFT OUTER JOIN outputs o ON m.id = o.machine_id
AND o.created_at >= '2017-07-25 05:00:00'
AND o.created_at < '2017-07-26 17:00:00'
WHERE m.machine_group_id = 1 ;
#1
1
Should be something like
应该是这样的
SELECT m.*
FROM machines m
LEFT OUTER JOIN outputs o ON m.id = o.machine_id
AND o.created_at >= '2017-07-25 05:00:00'
AND o.created_at < '2017-07-26 17:00:00'
WHERE m.machine_group_id = 1 ;