1

Rails 4, Active Record, Nitrous.io, Heroku, PostgreSQL.

The most_important_table in the Rails app I am coding contains records that each have a unique identifier (examples: "912828J27", "US38141EC238"), call it uniq_alphanum_identifier. My application (LEFT OUTER) joins several other tables' data ON most_important_table.uniq_alphanum_identifier.

Since I am not using my records' primary key for joining, I feel I am not leveraging my model associations and am finding myself writing SQL queries by hand using methods like .connection.select_all or .find_by_sql. But this doesn't seem "right." Why? SQL queries written by hand are tough to maintain and database dependent. "Last resort" is what the documentation calls these methods. (http://apidock.com/rails/ActiveRecord/Base/find_by_sql/class)

Question: how can I set up model associations using uniq_alphanum_identifier (is this even possible?) so that I can leverage my model associations and (hopefully) utilize Rails' query methods to eliminate writing queries by hand?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
brntsllvn
  • 931
  • 11
  • 18
  • 1
    Can't you use this unique identifier as the PK? PS: The "database dependent" argument is pretty weak, in my experience, ORMs don't really help you solve the hard portability problems, you have to do that stuff yourself with or without an ORM. – mu is too short Feb 12 '15 at 23:58
  • @muistooshort Good to know on the "database dependent" stuff and it looks like I can (re)define my primary key as answered [here](http://stackoverflow.com/questions/1200568/using-rails-how-can-i-set-my-primary-key-to-not-be-an-integer-typed-column) . Thanks for the nudge in the right direction! – brntsllvn Feb 13 '15 at 00:36
  • 2
    I think the current Rails way to set primary key is: `class Thing; self.primary_key = "uniq_alphanum_identifier"; end` – evanbikes Feb 13 '15 at 00:46

1 Answers1

2

You can specify foreign keys like so:

In a belongs_to

belongs_to :parent, foreign_key: 'parent_alphanum_id', primary_key: 'uniq_alphanum_identifier'

Or a has_many

has_many :children, foreign_key: 'parent_alphanum_id', primary_key: 'uniq_alphanum_identifier'

Then Rails should just work as usual.

evanbikes
  • 4,131
  • 1
  • 22
  • 17
  • This works. Thanks! Just ran a simple Class1.joins(:class2) in rails console and got the appropriate SQL "ON" association... I should have just given this a shot. Much appreciated. – brntsllvn Feb 13 '15 at 00:34