CODEWITHSUNDEEP
ruby-on-rails-4arel
rigyt
rigyt

Reputation: 2353

Rails 4 (arel?) join on subquery

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?

Upvotes: 1

Views: 916

Answers (1)

rigyt
rigyt

Reputation: 2353

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

Upvotes: 3

Related Questions