Table join sql to rails active record query

Question

How can I convert the following into an active record query:

SELECT reviews.style_id, AVG("col1"), AVG("col2")
  FROM reviews, audios
 WHERE reviews.consumer_id = audios.consumer_id
 GROUP BY style_id;

col1 and col2 belong to the audios table, but they are uniquely named (no similar column name in reviews), so there is no ambiguity error.

I am using PostgreSQL.

Answer 1

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