使用涉及多态关联的多连接的Rails ActiveRecord查询

Question

考虑到下面的模型定义，我试图弄清楚如何使用AR复制以下SQL查询。演员阵容是执行平均值所必需的。结果集应该按栏对foo进行分组(它来自多态关联)。任何帮助都是非常感谢的。

SQL：

SELECT AVG(CAST(r.foo AS decimal)) "Average", s.bar
FROM rotation r INNER JOIN cogs c ON r.cog_id = c.id 
           INNER JOIN sprockets s ON s.id = c.crankable_id
           INNER JOIN machinists m ON r.machinist_id = m.id
WHERE c.crankable_type = 'Sprocket' AND 
      r.machine_id = 123 AND 
      m.shop_id = 1
GROUP BY s.bar

ActiveRecord模型：

class Rotation < ActiveRecord::Base
    belongs_to :cog
    belongs_to :machinist
    belongs_to :machine
end

class Cog < ActiveRecord::Base
    belongs_to :crankable, :polymorphic => true
    has_many :rotation
end

class Sprocket < ActiveRecord::Base
    has_many :cogs, :as => :crankable
end

class Machinist < ActiveRecord::Base
    belongs_to :shop
end

更新

我想出了办法让它起作用，但感觉就像作弊。还有比这更好的方法吗？

Sprocket.joins('INNER JOIN cogs c ON c.crankable_id = sprockets.id', 
               'INNER JOIN rotations r ON r.cog_id = c.id',
               'INNER JOIN machinists m ON r.machinist_id = m.id')
    .select('sprockets.bar', 'r.foo')
    .where(:r => {:machine_id => 123}, :m => {:shop_id => 1})
    .group('sprockets.bar')
    .average('CAST(r.foo AS decimal)')

溶液

阿尔宾的回答不起作用，但确实让我找到了一个可行的解决方案。首先，我在Cog中有一个错误，必须将关系更改为：

has_many :rotation

以复数形式出现：

has_many :rotations

在此之后，我可以使用以下查询

Sprocket.joins(cogs: {rotations: :machinist})
    .where({ machinists: { shop_id: 1 }, rotations: { machine_id: 123}})
    .group(:bar)
    .average('CAST(rotations.foo AS decimal)')

唯一真正的区别是，我不得不分离where子句，因为机器不属于机械师。谢谢阿尔宾！

Answer 1

我认为这段代码有点简单，需要AR提供更多的帮助。

Sprocket
.joins(cogs: {rotations: :machinist})
.where({ machinists: { machine_id: 123, shop_id: 1 } } )
.group(:bar)
.average('CAST(rotations.foo AS decimal)')

select子句是不必要的，您不必选择值，因为您只需要在查询中在内部使用这些值，AR可以帮助您随后决定需要什么。

我在我自己的一个项目中使用了类似的结构进行了测试，但是它不是完全相同的模型，所以如果它不直接运行的话，可能会有一个错误或者什么东西在里面。我跑了：

Activity
.joins(locations: {participants: :stuff})
.where({ stuffs: { my_field: 1 } })
.group(:title)
.average('CAST(participants.date_of_birth as decimal)')

生成此查询

SELECT AVG(CAST(participants.date_of_birth as decimal)) AS average_cast_participants_date_of_birth_as_decimal, title AS title 
FROM `activities` 
INNER JOIN `locations` ON `locations`.`activity_id` = `activities`.`id` 
INNER JOIN `participants` ON `participants`.`location_id` = `locations`.`id` 
INNER JOIN `stuffs` ON `stuffs`.`id` = `participants`.`stuff_id` 
WHERE `stuffs`.`my_field` = 1 
GROUP BY title

哪个AR会变成如下所示的散列：

{"dummy title"=>#<BigDecimal:7fe9fe44d3c0,'0.19652273E4',18(18)>, "stats test"=>nil}