表连接的SQL轨活动记录查询 [英] Table join sql to rails active record query

问题描述

我如何转换以下为活动记录查询：

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 和 COL2 属于音频表，但他们唯一命名的（没有类似的列名评论），所以不存在歧义错误。

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.

我使用PostgreSQL的。

I am using PostgreSQL.

推荐答案

如果你有评论之间的关联和音频则是这样的：

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

这会给评论实例的转速列表键，这些实例将有额外的 avg_col1 和 avg_col2 访问的平均值，以及方法通常风格 / style_id 方法，但评论通常会提供将引发异常。另一列存取方法

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.

如果您还没有设置那么的关联，你可以做手工的JOIN：

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

如果你需要的是只是没有所有的ActiveRecord的包装和开销的原始数据，那么你可以执行原始的SQL，并使用手工hashify它<一个href="http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-select_rows"相对=nofollow> select_rows ：

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：不要使用隐式连接条件的SQL，这只是一个快速简便的方法来产生交叉产品，使用显式连接条件：

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

这篇关于表连接的SQL轨活动记录查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！