Rails Nested Joins Activerecord with conditions

Question

I'm trying to write a nested joins query with a condition.

The query I have right now is:

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

Which outputs the following 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 ' 

And also the following error:

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 ' 

It's telling me there is no column store.retailer_id, however, I can run the following query and it will work just fine:

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

Looks like you don't need nested joins here. Try to use something like

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

Nested join should also work using stores

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

Answer 2

There is the simplest way instead of using curly brackets :

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