28

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
strivedi183
  • 4,749
  • 2
  • 31
  • 38
stytown
  • 1,642
  • 2
  • 16
  • 22

3 Answers3

42

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}})
dimuch
  • 12,728
  • 2
  • 39
  • 23
  • 5
    Awesome, I needed to use "stores" instead of "store".... Such an easy fix. Thank you dimuch! – stytown Aug 04 '14 at 06:42
  • 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. – stephenmurdoch Mar 24 '18 at 03:17
  • 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)` – alecvn Oct 23 '18 at 10:04
  • 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. – aidan Sep 25 '21 at 00:14
6

There is the simplest way instead of using curly brackets : 

Event.joins(:store => :retailer).where('stores.retailer_id => ?', 2)
Rio Dermawan
  • 133
  • 1
  • 4
4

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

Panos Malliakoudis
  • 510
  • 4
  • 12