sethvargo sethvargo - 4 months ago 14
Ruby Question

Rails Joins and include columns from joins table

I don't understand how to get the columns I want from rails. I have two models - A User and a Profile. A User :has_many Profile (because users can revert back to an earlier version of their profile):

> DESCRIBE users;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | UNI | NULL | |
| password | varchar(255) | NO | | NULL | |
| last_login | datetime | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+


 

> DESCRIBE profiles;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| first_name | varchar(255) | NO | | NULL | |
| last_name | varchar(255) | NO | | NULL | |
| . . . . . . |
| . . . . . . |
| . . . . . . |
+----------------+--------------+------+-----+---------+----------------+


In SQL, I can run the query:

> SELECT * FROM profiles JOIN users ON profiles.user_id = users.id LIMIT 1;
+----+-----------+----------+---------------------+---------+---------------+-----+
| id | username | password | last_login | user_id | first_name | ... |
+----+-----------+----------+---------------------+---------+---------------+-----+
| 1 | john | ****** | 2010-12-30 18:04:28 | 1 | John | ... |
+----+-----------+----------+---------------------+---------+---------------+-----+


See how I get all the columns for BOTH tables JOINED together? However, when I run this same query in Rails, I don't get all the columns I want - I only get those from Profile:

# in rails console
>> p = Profile.joins(:user).limit(1)
>> [#<Profile ...>]
>> p.first_name
>> NoMethodError: undefined method `first_name' for #<ActiveRecord::Relation:0x102b521d0> from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.1/lib/active_record/relation.rb:373:in `method_missing' from (irb):8
# I do NOT want to do this (AKA I do NOT want to use "includes")
>> p.user
>> NoMethodError: undefined method `user' for #<ActiveRecord::Relation:0x102b521d0> from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.1/lib/active_record/relation.rb:373:in method_missing' from (irb):9


I want to (efficiently) return an object that has all the properties of Profile and User together. I don't want to :include the user because it doesn't make sense. The user should always be part of the most recent profile as if they were fields within the Profile model. How do I accomplish this?

I think the problem has something to do with the fact that the Profile model doesn't have attributes for User...

Answer

I don't think that you can load users and profiles with join in Rails. I think that in earlier versions of Rails ( < 2.1) loading of associated models was done with joins, but it was not efficient. Here you have some explanation and links to other materials.

So even if you explicite say that you want to join it, Rails won't map it to associated models. So if you say Profile.whatever_here it will always be mapped to Profile object.

If you still want to do what you said in question, then you can call custom sql query and process results by yourself:

p = ActiveRecord::Base.connection.execute("SELECT * FROM profiles JOIN users ON profiles.user_id = users.id LIMIT 1")

and get results row by row with:

p.fetch_row

It will already be mappet to an array.

Your errors are because you are calling first_name and user method on AciveRecord::Relation object and it stores an array of Profile objects, not a single object. So

p = Profile.joins(:user).limit(1)
p[0].first_name

shoud work.

Better way to fetch only one record is to call:

p = Profile.joins(:user).first
p.first_name
p.user

But when you call p.user it will query database. To avoid it, you can use include, but if you load only one profile object, it is useless. It will make a difference if you load many profiles at a time and want to inlcude users table.