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

Solution 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}})

Solution 2

There is the simplest way instead of using curly brackets :

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

Solution 3

You should be able to access the retailers id with the following syntax:

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

ps. tested on Rails 5

Comments

[stytown]

Awesome, I needed to use "stores" instead of "store".... Such an easy fix. Thank you dimuch!

[stephenmurdoch]

Ditto @stytown - it has taken me DAYS to realise that even though I'm using a :has_one relationship, I need to pluralize the association name in the where clause. You just saved my hide.

[alecvn]

Just a note that this becomes quite a bit uglier once you have namespaces on your tables: Event.joins(:store).where("namespace_stores.retailer_id = ?", 2)

[aidan]

This answer is still saving our hides 7 years later (or at least just saved mine). For all that I don't understand this pluralization convention, it's nice that Rails hasn't changed its mind back and forth in the years since.