Rails嵌套连接Activerecord with conditions

Question

我正在尝试编写一个带有条件的嵌套连接查询。

我现在的查询是：

Event.joins(:store => :retailer).where(store: {retailer: {id: 2}})

它输出以下SQL：

   SELECT "events".* FROM "events" INNER JOIN "stores" ON "stores"."id" = "events"."store_id" INNER JOIN "retailers" ON "retailers"."id" = "stores"."retailer_id" WHERE "store"."retailer_id" = '---
:id: 2
'

还有下面的错误：

SQLite3::SQLException: no such column: store.retailer_id: SELECT "events".* FROM "events" INNER JOIN "stores" ON "stores"."id" = "events"."store_id" INNER JOIN "retailers" ON "retailers"."id" = "stores"."retailer_id" WHERE "store"."retailer_id" = '---
:id: 2
'

它告诉我没有列store.retailer_id，但是，我可以运行以下查询，它将工作得很好：

Event.first.store.retailer_id
  Event Load (0.2ms)  SELECT  "events".* FROM "events"   ORDER BY "events"."id" ASC LIMIT 1
  Store Load (0.1ms)  SELECT  "stores".* FROM "stores"  WHERE "stores"."id" = ? LIMIT 1  [["id", 28958]]
=> 4

Answer 1

看起来你在这里不需要嵌套连接。尝试使用像这样的东西

Event.joins(:store).where(stores: {retailer_id: 2})

嵌套连接也应该使用stores

Event.joins(:store => :retailer).where(stores: {retailer: {id: 2}})

Answer 2

有一种最简单的方法，而不是使用大括号：

Event.joins(:store => :retailer).where('stores.retailer_id => ?', 2)

Answer 3

您应该能够使用以下语法访问retailers id：

Event.joins(:store => :retailer).where('retailers.id' => 2)

ps。在Rails 5上进行了测试