rails 3 has_and_belongs_to_many

Question

I have a legacy database. Users have many accounts, and Accounts have many users. There is a join table called UserAccountMapping:

=====================
|UserID  | AccountID|
=====================

Both columns are var chars types.

In Account.rb:

has_and_belongs_to_many :users, :join_table => :UserAccountMapping, :foreign_key => :AccountID, :association_foreign_key => :UserID

In User.rb:

has_and_belongs_to_many :accounts, :join_table => 'UserAccountMapping', :foreign_key => :UserID, :association_foreign_key => :AccountID

Users also have a :email and :id column. The primary key for users is :id (which is an integer), and :email is a string.

When I do something like User.first.accounts, the WHERE clause of the join SQL will try to match UserAccountMapping.UserID to the :id of the user, but I want it to use the user's :email. Is this possible?

So, the SQL it generates is:

SELECT "Account".* FROM "Account" INNER JOIN "UserAccountMapping" ON "Account"."AccountID" = "UserAccountMapping"."AccountID" WHERE "UserAccountMapping"."UserID" = 3

Instead, I want this:

SELECT "Account".* FROM "Account" INNER JOIN "UserAccountMapping" ON "Account"."AccountID" = "UserAccountMapping"."AccountID" WHERE "UserAccountMapping"."UserID" = "user@something.com"

Answer 1

The way you've structured the data here is so anti-Rails that you're going to have to fight to get it to work. That's the trouble with convention. If you run against the grain it gets very difficult to implement.

Using non-standard column names like UserID instead of user_id creates enormous amounts of complexity in your application. If possible, switch to the Rails style and save yourself all that trouble of having to over-ride everything.

You should also avoid using has_and_belongs_to_many as it's a relic from Rails 1 that isn't as useful as the has_many :through feature in Rails 2+. All you need is an id primary-key column in your join table and an associated model file that sets up the two belongs_to relationships.

Using a non-standard id column is also going to cause trouble and should only be done as a last resort. Most of the time you can work around this using a UNIQUE index on that column and the finder method for it:

@user = User.find_by_email(email)
@account = @user && @user.accounts.first

If you follow the "Rails way" things work a lot better. A good part of learning Rails is knowing when to play along and when and how to buck convention and go your own way without getting into trouble.