#3 楼 @flemon1986
我找到办法了
class Product
has_many :reviews
has_one :review_summary
end
class ReviewSummary < ApplicationRecord
self.table_name = 'reviews'
default_scope {
from('products').
joins('INNER JOIN reviews ON reviews.product_id = products.id').
select('products.id as product_id, avg(stars) as avg_star')
}
end
然后
p = Product.includes(:review_summary).first
p.review_summary.avg_star # => 平均评分
这样就可以利用 includes 把统计数据都查出来了,生成的查询是这样的
SELECT products.id as product_id, avg(stars) as avg_star FROM products INNER JOIN reviews ON reviews.product_id = products.id WHERE "reviews"."product_id" IN (1, 2)
上面是用 reviews.product_id 用来做 where 条件的,可能有点巧合的感觉,一种更好的配法是
class ReviewSummary < ApplicationRecord
self.table_name = 'products'
default_scope {
joins('INNER JOIN reviews ON reviews.product_id = products.id').
select('products.id as id, avg(stars) as avg_star')
}
end
class Product < ApplicationRecord
has_many :reviews
has_one :review_summary, foreign_key: :id
end
生成的查询是:
SELECT products.id as product_id, avg(stars) as avg_star FROM products INNER JOIN reviews ON reviews.product_id = products.id WHERE "products"."id" IN (1, 2)
用 products.id 来写 where 正是我们想要的行为,完美。。这样虚拟出关联还是相当好玩的,不用 create view 了