Rails Joins and include columns from joins table

Question

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...

Why do you want to have the user fields inside the profile model? You clearly have two separate models, profile and user. By using include you can fetch data for both entities in one single query and then just do profile.user — aromero, Dec 30 '10 at 22:45

Lex Lindsey · Answer 1 · 2013-06-07T15:44:24.480

Use select() to name the columns you want. At least this works in Rails 3.0.9.

Background: my application has a primary table named :rights. I wanted to be able to ascribe a tag and color to a given :right record so I could easily pick it out of an index listing. This doesn't cleanly fit the Rails picture of associated records; most :rights will never be tagged, and the tags are completely arbitrary (user input via tag/edit).

I could try duplicating the tag data in the :right record, but that violates normal form. Or I could try querying :tags for each :right record, but that is a painfully inefficient approach. I want to be able to join the tables.

MySQL console shows:

mysql> describe rights;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |

  ...

| Tagid      | int(11)       | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

mysql> describe tags;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| TagName    | varchar(255) | YES  |     | NULL    |                |
| TagColor   | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
| updated_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

I am going to use TagName and TagColor in views/rights/index.html.erb, so I want the rights controller to include those columns in the @rights object it passes to the view. Since not every :right has a :tag, I want to use an outer join:

@rights = Right.joins("LEFT OUTER JOIN tags ON rights.Tagid = tags.id")

But, as everyone has found, this alone doesn't work: a block reference to TagName produces a server error. However, if I add a select at the end, all is well:

@rights = Right.joins("LEFT OUTER JOIN tags ON rights.Tagid = tags.id").select("rights.*,tags.TagName as TagName,tags.TagColor as TagColor")

Note added 6/7/13: the select clause does not require aliases - this works too:

.select("rights.*,tags.TagName,tags.TagColor")

Now I can reference TagName and TagColor in my view:

<% @rights.each do |right| %>
  <tr ALIGN=Left <%=
  # color background if this is tagged
  " BGCOLOR=#{right.TagColor}" if right.TagColor
  %> > ...
<% end %>

Note that typing `@rights` in the console will not display the extra tag elements. But typing `@rights.TagName` will show the values. — Doug, May 08 '14 at 20:13
I think that is because of Rails lazy loading; the database is not actually read until a specific column value is required. — Lex Lindsey, Aug 06 '14 at 03:45

score 12 · Accepted Answer · edited May 23 '17 at 10:29

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.

score 8 · Answer 3 · edited May 12 '16 at 03:45

8

Try using select("*").joins(:table)

In this case, you would type:

User.select("*").joins(:profile)

Hope that works for you.

edited May 12 '16 at 03:45

Micho

3,929
13
37
40

answered May 12 '16 at 02:32

snoopy0123

81
1
3

Do you know if there is any way to use `includes` instead of `joins` in this case? I want to do the same thing but with `LEFT` instead of `INNER` join – molexi Dec 02 '20 at 16:51
Not sure if this completely answers the question. Only the left table's data is printed to the console when I do it. – David Apr 30 '21 at 08:56

score 5 · Answer 4 · answered Aug 02 '16 at 04:17

After reading these tips I got the joins to all be loaded in one query by reading 3 ways to do eager loading (preloading) in Rails 3 & 4.

I'm using Rails 4 and this worked like a charm for me:

refs = Referral.joins(:job)
          .joins(:referee)
          .joins(:referrer)
          .where("jobs.poster_id= ?", user.contact_id)
          .order(created_at: :desc) 
          .eager_load(:job, :referee, :referrer)

Here were my other attempts.

#first attempt
#refs = Referral.joins(:job)
#          .where("jobs.poster_id= ?", user.contact_id)
#          .select("referrals.*, jobs.*")
# works, but each column needs to be explicitly referenced to be used later.
# also there are conflicts for columns with the same name like id

#second attempt
#refs = ActiveRecord::Base.connection.exec_query("SELECT jobs.id AS job_id, jobs.*, referrals.id as referral_id, referrals.* FROM referrals INNER JOIN jobs ON job_id = referrals.job_id WHERE (jobs.poster_id=#{user.contact_id});")
# this worked OK, but returned back a funky object, plus the column name
# conflict from the previous method remains an issue.


#third attempt using a view + rails_db_views
#refs = JobReferral.where(:poster_id => user.contact_id)
# this worked well. Unfortunately I couldn't use the SQL statement from above
# instead of jobs.* I had to explicitly alias and name each column.
# Additionally it brought back a ton of duplicate data that I was putting
# into an array when really it is nice to work with ActiveRecord objects.

#eager_load
#refs = Referral.joins(:job)
#          .where("jobs.poster_id= ?", user.contact_id)
#          .eager_load(:job)
# this was my base attempt that worked before I added in two more joins :)

score 2 · Answer 5 · answered Nov 16 '14 at 10:29

I have got round this problem by creating a VIEW in the database which is the join, and then referencing that as if it were a normal ActiveRecord table in the code. This is fine for getting data out of the database, but if you need to update it, then you'll need to go back to the base classes that represent the 'real' tables. I have found this method to be handy when doing reports that use biggish tables - you can get the data out all in one hit. I am surprised that this doesn't seem to be built into ActiveRecord, seems an obvious thing to me!

So for you:

IN SQL:

CREATE VIEW User_Profiles
AS
SELECT P.*, U.first_name
FROM Users U
inner join Profiles P on U.id=P.user_id

IN RUBY models file:

class UserProfile < ActiveRecord::Base
  self.primary_key = :id 
  #same dependencies as profiles
end

**HINT... I always forget to set the owner of the view (I use postgres), so it blows up straight away with much cursing and self-recrimination.

Rails Joins and include columns from joins table

5 Answers5

Linked