2 ms·
Here's one that's been bugging me - anyone want to take a crack at it? @dev_configs = DeviceConfig. joins("join (select device_id, max(updated_at) as m
by davidw 14y ago
Here's one that's been bugging me - anyone want to take a crack at it?
@dev_configs = DeviceConfig.
joins("join (select device_id, max(updated_at) as max_updated_at
from device_configs group by device_id) dc2
on dc2.device_id = device_configs.device_id and
dc2.max_updated_at = device_configs.updated_at").
includes("device").order("devices.updated_at desc")
Each device has many device configurations, and we want to display all devices, along with the latest device configuration, and order the whole thing by when the device was updated. The above works, but is mostly working directly in SQL, rather than with Rails.
- emiller829 14y agoIn stock ActiveRecord, you're not going to be able to join a derived table without dropping to SQL. :( Still, if you're looking to constrain results to only the latest, you could use the having clause (with some performance penalty).
- omarqureshi 14y agoThis is something you should be able to do with Arel (with join_sources) or through the use of to_sql as a note - you shouldn't use includes and joins, includes hammers joins. Simplest tidy up though is a scope for the join, something like DeviceConfig.joins("#{DeviceConfig.last_updated.to_sql} dc2 on dc2.device_id = device_configs.device_id and dc2.max_updated_at = device_configs.updated_at). order(...) Alternatively maybe something like dc_t = Arel::Table::new :device_configs dc2_t = Arel::Table::new :device_configs inner = dc_t.group.(dc_t[:device_id]). project(dc_t[:device_id].as("device_id"), dc_t[:updated_at].maximum.as("maximum_updated_at")) dc2_t.join(inner.join_sources). on(dc2_t[:device_id].eq(inner[:device_id])). on(dc2_t[:updated_at].eq(inner[:max_updated_at])). order(...) im not entirely sure of the on syntax there, the API doesnt look clear. Atleast, those are the two solutions I have though personally - i think it reads better as straight SQL with select_values. That way, you know what is happening.