Doing JOINs the Rails way

April 18, 2008 1:37 PM

How can I do a Rails find JOIN without SQL, using the two tables' existing has_and_belongs_to_many relationships?

I have two classes Sample and User, which have:

has_and_belongs_to_many :users, :uniq => true

has_and_belongs_to_many :samples, :uniq => true

relationships in their respective model files. This sets up the many-to-many relationship between the two classes.

Let's say I want to associate a Sample instance with a User instance:

Sample.find_by_id(params[:sample][:id]).users << User.find_by_id(params[:user_id])

So far so good.

But I'd like to avoid duplicates of a combination of sample_id and user_id.

Therefore, I'd like to search on samples_users to find a previously-entered record which matches those two id numbers.

I could do this with a SQL JOIN but I'd like to learn the Rails way, so that I don't have to write custom SQL that will likely break during development.

How to I use the find method on Sample to get any matches with a specified sample_id and user_id?

posted by Blazecock Pileon to Computers & Internet (5 answers total)

Best answer: This kind of sidesteps your question, but avoiding duplicates can be done at the database level by creating a unique index across sample_id and user_id in the samples_users table like so:

add_index :samples_users, [:sample_id, :user_id], :unique => true

There's a chance you'll need a begin / rescue for saving - I'm not sure if it just returns false when things don't work out or if AR throws an exception.
posted by soma lkzx at 2:03 PM on April 18, 2008

Best answer: sample = Sample.find_by_id(params[:sample][:id])
sample.users << User.find_by_id(params[:user_id]) unless sample.users.find(params[:user_id])
posted by bitdamaged at 2:10 PM on April 18, 2008

Response by poster: Thank you both kindly. I wish there were better tutorials for Rails.
posted by Blazecock Pileon at 3:36 PM on April 18, 2008

The best part about RoR is that according to google ten thousand sites have the answer you're looking for, but when you try to visit them you get nothing but that stellar "Application Error". Does a lot to instill confidence!
posted by soma lkzx at 7:33 PM on April 18, 2008

Response by poster: Your advice was perfect, soma lkzx, and for other's benefit, I did need to handle the exception:

def self.add_sample_user_association(params)
  begin
    Sample.find_by_id(params[:sample][:id]).users << User.find_by_id(params[:user_id])
    return true
  rescue ActiveRecord::StatementInvalid => error
    return false
  end
end
posted by Blazecock Pileon at 2:12 PM on April 19, 2008