Table join sql to rails active record query

activerecord, postgresql, ruby-on-rails

Solution

If you have an association between `Review` and `Audio` then something like this:

revs = Review.joins(:audios)
             .group('style_id')
             .select('style_id, avg(col1) as avg_col1, avg(col2) as avg_col2')

That will give a list of `Review` instances in `revs` and those instances will have extra `avg_col1` and `avg_col2` methods for accessing the averages as well as the usual `style`/`style_id` methods but the other column accessor methods that `Review` would normally offer will raise exceptions.

If you don't have the associations set up then you can do the JOIN manually:

revs = Review.joins('join audios on reviews.consumer_id = audios.consumer_id')
             .group('style_id')
             .select('style_id, avg(col1) as avg_col1, avg(col2) as avg_col2')

If all you need is just the raw data without all the ActiveRecord wrapping and overhead, then you could execute the raw SQL and hashify it by hand using `select_rows`:

Review.connection.select_rows(%q{
    select r.style_id, avg(a.col1), avg(a.col2')
    from reviews r
    join audios  a on r.consumer_id = a.consumer_id
    group by r.style_id
}).map do
  { :style_id => r.shift, :avg_col1 => r.shift.to_f, :avg_col2 => r.shift.to_f }
end

That would give you an Array of Hashes. You could even simplify that approach using `Struct` to create simple data wrapper classes:

c    = Struct.new(:style_id, :avg_col1, :avg_col2)
revs = Review.connection.select_rows(%q{...}).map do |r|
  c.new(r.shift, r.shift.to_f, r.shift.to_f)
end

PS: Don't use implicit join conditions your SQL, that's just a quick and easy way to produce cross products, use explicit join conditions:

SELECT ...
  FROM reviews JOIN audios ON reviews.consumer_id = audios.consumer_id
 GROUP BY style_id

Problem

Original source