Geek asks and answers

Join sql to rails table active write query

How can I convert the following to an active write request:

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

col1and col2refer to the audio table, but they are uniquely named (there is no similar column name in reviews), so there is no ambiguity error.

I am using PostgreSQL.

+3
source share
1 answer

If you have a connection between Reviewand 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')

Review revs, avg_col1 avg_col2 , style/style_id, , Review, .

, JOIN :

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')

, , ActiveRecord, SQL , 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

. , Struct :

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: SQL, , :

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

More articles:


All Articles