How to join multiple tables with one to one relationships in rails

Question

Ruby on Rails is very new to me. I am trying to retrieve set of columns from 3 different tables. I thought I could use SQL view to retrieve my results but could not find a way to use views in Rails. Here are my tables. 1) User table --> user name, password and email 2) UserDetails table --> foreign key: user_id, name, address1, city etc. 3) UserWorkDetails --> foreign key: user_id, work address1, work type, etc

These 3 tables have one to one relationships. So table 2 belongs to table 1 and table 3 also belongs to table 1. Table 1 has one userdetails and one userworkdetails.

I want to get user email, name, address1, city, work address1, work type using joins.

What is the best way to handle this?

Answer 1

To get what you want done quickly use the :include option to include both the other tables when you query the primary table, so:

some_user_details = User.find(some_id, :include => [:user_details, :user_work_details])

This will just load all of the fields from the tables at once so there's only one query executed, then you can do what you need with the objects as they will contain all of the user data.

I find that this is simple enough and sufficient, and with this you're not optimising too early before you know where the bottlenecks are.

However if you really want to just load the required fields use the :select option as well on the ActiveRecord::Base find method:

some_user_details = User.find(some_id, :include => [:user_details, :user_work_details], :select => "id, email, name, address1, city, work_address1")

Although my SQL is a bit rusty at the moment.

Answer 2

User.find(:first, :joins => [:user_work_details, :user_details], :conditions => {:id => id})

I'd say that trying to just select the fields you want is a premature optimization at this point, but you could do it in a complicated :select hash.

:include will do 3 selects, 1 on user, one on user_details, and one on user_work_details. It's great for selecting a collection of objects from multiple tables in minimum queries.

http://apidock.com/rails/ActiveRecord/Base/find/class

Answer 3

The data is (are) in the models. Everything else is just an optimization. So address1 is at user.user_detail.address1, for instance.

if you have

class User
  has_one :user_detail
  has_one :user_work_detail
end
class UserDetail
  belongs_to :user
end
class UserWorkDetail
  belongs_to :user
end

With user_id columns in tables named user_details and user_work_details then everything else is done for you. If you later need to optimize you can :include the owned models, but it's not necessary for everything to work.