CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
adass
adass

Reputation: 345

nested SQL SELECT in AR query

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.

SQL code:

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;

Upvotes: 0

Views: 103

Answers (1)

Shawn
Shawn

Reputation: 4786

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 ;

Upvotes: 1

Related Questions