在 Rails3 中使用 ARel 构建子查询 [英] Building a subquery with ARel in Rails3

问题描述

我正在尝试在 ARel 中构建此查询:

I am trying to build this query in ARel:

SELECT FLOOR(AVG(num)) FROM (
SELECT COUNT(attendees.id) AS num, meetings.club_id FROM `meetings` INNER JOIN `attendees` ON `attendees`.`meeting_id` = `meetings`.`id` WHERE (`meetings`.club_id = 1) GROUP BY meetings.id) tmp
GROUP BY tmp.club_id

它返回每个俱乐部每次会议的平均出席人数.(一个俱乐部有很多会议，一个会议有很多参与者)

It returns the average number of attendees per meeting, per club. (a club has many meetings and a meeting has many attendees)

到目前为止我已经(在类 Club < ActiveRecord::Base 中声明):

So far I have (declared in class Club < ActiveRecord::Base):

num_attendees = meetings.select("COUNT(attendees.id) AS num").joins(:attendees).group('meetings.id')
Arel::Table.new('tmp', self.class.arel_engine).from(num_attendees).project('FLOOR(AVG(num))').group('tmp.club_id').to_sql

但是，我收到错误:

undefined method `visit_ActiveRecord_Relation' for #<Arel::Visitors::MySQL:0x9b42180>

生成非平凡 ARel 查询的文档有点难找.我一直在使用 http://rdoc.info/github/rails/arel/master/frames 我是否错误地处理了这个问题?还是我离解决方案还有一些方法?

The documentation for generating non trivial ARel queries is a bit hard to come by. I have been using http://rdoc.info/github/rails/arel/master/frames Am I approaching this incorrectly? Or am I a few methods away from a solution?

推荐答案

当您在 Arel 中构建复杂的完整查询时，无法将其转换为正确的 ActiveRecord 对象列表.只能使用 Arel 中的谓词，即您可以在 .where() 函数中指定的内容.

When you build conplicated, full queries in Arel, there's no way to turn that into a list of proper ActiveRecord objects. Only the predicates in Arel, the things you can specify inside a .where() function, can be used.

然而，这些优势被简洁地包含在 meta_where gem 中:

These advantages, however, are succinctly wrapped up into the meta_where gem:

http://metautonomo.us/projects/metawhere/

听取他们关于添加的建议

Take their advice about adding

MetaWhere.operator_overload!

给你的

config/initializers/meta_where.rb

以便您可以使用添加到符号的[]"函数来执行操作:

so that you can do things with the '[]' function added to symbol:

Attendee.select(:count[:id].as('person_count')).group(:id)

这篇关于在 Rails3 中使用 ARel 构建子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！