Rails has_many :through with custom foreign_key

Question

I have the following set of models:

class Cardstock < ActiveRecord::Base
  has_many :color_matches, :primary_key => :hex, :foreign_key => :hex
  has_many :palette_colors, :through => :color_matches
end

class ColorMatch < ActiveRecord::Base
  belongs_to :palette_color
  has_many :cardstocks, :foreign_key => :hex, :primary_key => :hex
end

class PaletteColor < ActiveRecord::Base
  has_many :color_matches
  has_many :cardstocks, :through => :color_matches
end

Calling Cardstock.last.palette_colors yields the following error:

ActiveRecord::StatementInvalid: PGError: ERROR:  operator does not exist: character varying = integer
LINE 1: ...".palette_color_id    WHERE (("color_matches".hex = 66))  OR...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "palette_colors".* FROM "palette_colors"  INNER JOIN "color_matches" ON "palette_colors".id = "color_matches".palette_color_id    WHERE (("color_matches".hex = 66))  ORDER BY name ASC

This shows me that the query ActiveRecord generates is using the cardstock's id (66) where it should be using the cardstock's hex (bbbbaf). Somewhere, I need to specify to ActiveRecord to use the hex column to join between cardstocks and color_matches. Does ActiveRecord support this?

This is Rails 2.3.x, by the way. – Alex Kahn Jun 13 '11 at 14:52 — Alex Kahn, Jun 13 '11 at 14:52

score 2 · Answer 1 · answered Jun 13 '11 at 15:05

2

Your relationships are all out of whack here.

the relationships between Cardstock and ColorMatch should be a has_and_belongs_to_many relationship on both sides
anywhere you have a has_many relationship, you need a corresponding belongs_to relationship in the corresponding class

answered Jun 13 '11 at 15:05

jefflunt

33,527
7
88
126

4

That's not quite true. There's absolutely nothing wrong with using `has_many :through` instead of `has_and_belongs_to_many`; I believe it's even the preferred way these days. However, you are correct that there's something wrong with the relationships. – Emily Jun 13 '11 at 15:12
Thank you, I need to change the `has_many` call in ColorMatches to a `belongs_to`. But I think there's more to the issue, because that bad query is still generated. – Alex Kahn Jun 13 '11 at 15:22

score 1 · Answer 2 · answered Jun 13 '11 at 15:17

There's something wrong with the way your relationships are set up. I don't quite understand your specific use case here, so I'm not sure where the problem is. The way to think about this is probably as a many-to-many relationship. Figure out what the two sides of that many-to-many are, and what's the join model. I'm going to give an example assuming that ColorMatch is your join model -- it's what relates a PaletteColor to a Cardstock. In that case, you'll want your relationships to look something like this:

class Cardstock < ActiveRecord::Base
  has_many :color_matches, :primary_key => :hex, :foreign_key => :hex
  has_many :palette_colors, :through => :color_matches
end

class ColorMatch < ActiveRecord::Base
  belongs_to :palette_color
  belongs_to :cardstocks, :foreign_key => :hex, :primary_key => :hex
end

class PaletteColor < ActiveRecord::Base
  has_many :color_matches
  has_many :cardstocks, :through => :color_matches
end

In terms of your database, you should have a palette_color_id and a hex field on the color_matches table, and a hex field on the cardstocks table.

Exactly! However, after setting up my associations like that, I still get the same error. Does ActiveRecord support this? Or do I need to set up another table, just for the hexes, that `cardstocks` and `color_matches` would both point to with integer columns? — Alex Kahn, Jun 13 '11 at 15:30

Rails has_many :through with custom foreign_key

2 Answers2