rails nested has_many with foreign scope join misses to join table: sql error

Question

A self running template with all models to test by yourself is available in this github gist - run it and it triggers the error.

To visualize it, the structure looks like this:

Colors
  |n|
  |:|
  |1| -----
Houses n:n Conditions
  |n| -----
  |:|
  |1|
People

Starting from the blank db I create some test data (console commands, return values omitted to keep it clear):

irb(main):001:0> Condition.create(condition: :damaged)
irb(main):002:0> house = House.create(conditions: [Condition.first])
irb(main):003:0> person = Person.create
irb(main):004:0> house.person = person
irb(main):005:0> house.save

So now I have some test data. Let's retrieve the person's houses (which are only the damaged ones by definition):

irb(main):006:0> person.damaged_houses
  House Load (0.2ms)
  SELECT "houses".* FROM "houses" 
    INNER JOIN "conditions_houses" ON "conditions_houses"."house_id" = "houses"."id"
    INNER JOIN "conditions" ON "conditions"."id" = "conditions_houses"."condition_id"
    WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'
    [["person_id", 1]]
=> #<ActiveRecord::Associations::CollectionProxy [#<House id: 1, person_id: 1>]>

All good, the damaged house is returned and the sql joined the conditions table correctly. Now I want to get all colors of the person, which is defined as all colors of the houses, where the houses are still only the damaged ones. This should return an empty collection (since no colors are in the db yet).

irb(main):007:0> person.damaged_colors
  Color Load (0.4ms) 
  SELECT "colors".* FROM "colors"
    INNER JOIN "houses" ON "colors"."house_id" = "houses"."id" 
    WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'
    [["person_id", 1]]
SQLite3::SQLException: no such column: conditions.condition:
  SELECT "colors".* FROM "colors"
    INNER JOIN "houses" ON "colors"."house_id" = "houses"."id"
    WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'

It's clear from the sql string that the join table conditions is missing and therefore conditions.condition is not available. If I see it correctly, simply this string from the query before is missing:

INNER JOIN "conditions_houses" ON "conditions_houses"."house_id" = "houses"."id"
INNER JOIN "conditions" ON "conditions"."id" = "conditions_houses"."condition_id"

So the query should be:

SELECT "colors".* FROM "colors"
  INNER JOIN "houses" ON "colors"."house_id" = "houses"."id"
  INNER JOIN "conditions_houses" ON "conditions_houses"."house_id" = "houses"."id"
  INNER JOIN "conditions" ON "conditions"."id" = "conditions_houses"."condition_id"
  WHERE "houses"."person_id" = ? AND "conditions"."condition" = 'damaged'

Is this a rails bug or am I doing it wrong? Why is the join conditions missing?

The code:

class Color < ActiveRecord::Base
    belongs_to :house
end

class Condition < ActiveRecord::Base
    has_and_belongs_to_many :houses
end

class House < ActiveRecord::Base
    has_many :colors
    belongs_to :person

    has_and_belongs_to_many :conditions

    scope :damaged, -> { joins(:conditions).where(:'conditions.condition' => 'damaged') }
end

class Person < ActiveRecord::Base
    has_many :damaged_houses, -> { damaged }, :class_name => "House"
    has_many :damaged_colors, through: :damaged_houses, :source => :colors
end

I'm pretty sure it's a bug, it's very similar to typical ActiveRecord associations' bugs (see for example [this](https://github.com/rails/rails/pull/11518)) — mdesantis, Feb 20 '14 at 23:06
It would help 1000x if you posted the models, specifically the associations/methods used do derive `damaged_colors` — omarvelous, Feb 21 '14 at 18:43
@omarvelous please see the link to my gist https://gist.github.com/doits/60b862bb45855b506ad9 (which you find at the top of my question btw) — Markus, Feb 21 '14 at 21:56
@markus Bring your bugs to the people, don't make the people go to your bugs. — Dave Newton, Feb 21 '14 at 21:58
Interesting... do you happen to have a `person.colors` without the `damaged` scope and is that working? — omarvelous, Feb 21 '14 at 22:08
The other suggestion would be to not use HABTM... and create a join model that bt both, and both hm of... — omarvelous, Feb 21 '14 at 22:13
@omarvelous I updated the gist to test unscoped has_many colors (and it runs through). — Markus, Feb 21 '14 at 22:18
Am I missing something? If there are no colors defined yet, the db columns are null; to get these rows you need to make rails do an outer join (all values from the other table even if they don't match equality). I would have put this as an actual answer if I had tried it and provided a solution. Maybe something like http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3? By the way: if we could wager points here on SO, I would bet 500 that this is not a rails bug, as it's really a common problem, unless I am misreading (which is also,a common problem :-) — Tom Harrison, Feb 26 '14 at 06:02
Even if there are colors it does not work since the join is still missing. The idea is to go over damaged houses to the colors by the `has_many :damaged_colors`. Can you please provide the example with outer join? You can simply download the linked gist and run it with `ruby` to test it, so you get it up fast. — Markus, Feb 26 '14 at 10:10
Aren't colors and conditions really just accessors (db columns) on a house? why do they have their own class? This seems like unnecessary complexity. This immensely simplifies the problem and the SQL. You add columns for color and condition to house. — engineerDave, Feb 27 '14 at 02:18

score 1 · Answer 1 · edited Feb 27 '14 at 16:10

1

So I was able to get your gist to run by modifying the following:

has_many :damaged_colors, through: :damaged_houses, :source => :colors

to

has_many :damaged_colors, -> { joins({house: :conditions}).where(:'conditions.condition' => 'damaged') }, through: :damaged_houses, :source => :colors

It does appear to be an ActiveRecord bug on HABTM associations as alluded to in the comments... Hope this helps in the mean time...

Funny thing was, I converted your gist to a hm:hm and had the same issue...

edited Feb 27 '14 at 16:10

Coenwulf

1,937
2
15
23

answered Feb 21 '14 at 22:49

omarvelous

2,774
14
18

thanks, this helps for now to get the code running but obviously duplicates the scope definition - so this can only be a temporary solution. – Markus Feb 22 '14 at 15:54

rails nested has_many with foreign scope join misses to join table: sql error

1 Answers1