Rails 4 (arel?) join on subquery

Question

My Product table has multiple versions of each product at different dates.

I want to find each max(date), grouped by version_id. And I want an ActiveRecord::Relation of the rows that correspond to that max date and version_id.

in app/models/product.rb :

 def self.latest_versions select("max(date) as max_date"). select("version_id"). group("version_id") end

How to I join Product with this "temporary table" to get all products matching the date and version ids returned? I obviously can't select :id in the method above as it's not a Group By column.

The actual scenario is more complex but maybe I'm missing a basic principle that falls under this simplified scenario?

rigyt · Accepted Answer · 2015-02-20 11:21:24Z

def self.latest_versions t = Product.arel_table subquery = t. project( t[:date].maximum.as("max_date"), t[:version_id] ). group(t[:version_id]). as('subquery') Product. joins(Arel::Nodes::InnerJoin.new(subquery, Arel::Nodes::On.new( t[:version_id].eq(subquery[:version_id]).and( t[:date].eq(subquery[:max_date]))))) end

Excellent! I was trying to use join(subquery, Arel::Nodes::OuterJoin) (something that's been suggested elsewhere), but it was having trouble navigating that. Rails (or whatever) would complain about being unable to visit SelectManager and other related objects (depending on what permutation of my frustrated coding-trys I was in ;) )

Collectives™ on Stack Overflow

Rails 4 (arel?) join on subquery

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related