Rails Join Table Problem

Question

I am working on a rails project and am having some issues with the following join:

@page = Page.find(params[:id], :joins => "LEFT JOIN page_translations ON page_translations.page_id = pages.id")

For some reason its only pulling back everything from the Pages table.

Here is my model for Page

class Page < ActiveRecord::Base
  has_many :users_pages
  has_many :users, :through => :users_pages
  has_many :page_translations
  has_many :categories
  accepts_nested_attributes_for :page_translations
  accepts_nested_attributes_for :categories
end

Here is my model for PageTranslation

class PageTranslation < ActiveRecord::Base
  belongs_to :pages
end

Thanks in advance for all of the help!

Edit (@thenduks)

The log runs two separate queries:

Page Load (0.5ms)  SELECT `pages`.* FROM `pages` WHERE (`pages`.`id` = 1) LIMIT 1

PageTranslation Load (0.5ms) SELECT `page_translations`.* FROM `page_translations` WHERE (`page_translations`.page_id = 1)

Here is what my controller looks like:

@page = Page.find(params[:id], :include => :page_translations)

Answer 1

How about:

Page.find( params[:id], :include => :page_translations )

Edit:

Ok, so some time recently the behavior of ActiveRecord when it comes to joins/includes seems to have changed. The guides still refer to being able to do this though two associations, as in has_many :orders, :include => :line_items and similar... but as far as including records from a has_many... After consulting with a co-worker we came across some info on the subject. Seems that the single monolithic queries were just getting too complex and ugly and it was causing problems for some of the fancier niceties that ActiveRecord gives you and duplicate rows, that kind of thing.

TL;DR: It doesn't work like that anymore. 2 queries is expected.

Answer 2

I was stumped about this same thing and wasted a few hours trying to figure it out. It turns out that using the joins method of the query interface doesn't initialize the models related to the tables being joined. You can see this by watching the SQL statements in the server console, or by even redirecting ActiveRecord logging to STDOUT in your Rails console.

I was very disappointed by this. It just doesn't seem like how the joins method should work -- it certainly wasn't what I was expecting. I was expecting it to eager load, since it was in the eager load section of the Edge Guides.

Anyway, I couldn't waste any more time trying to figure it out, so what I did instead is use the fancy query interface to simply build my query, used to_sql to get the SQL for my query, and then passed the SQL to select_all, which returns an array of hashes, where each element in the array (each hash) represents a row.

Example:

query = Post.joins("LEFT JOIN categories ON post.category_id = categories.id")
query.select("posts.*, category.category_name")
con = ActiveRecord::Base.connection
results = con.select_all(query.to_sql)

Results:

[{"id": 1, "title": "joins, why have you forsaken me", "category_name": "frustration"},{"id": 2, "title": "pizza", "category_name": "food"}]

To be honest, I would still like to know for certain if it is possible to do it the way we think it should work, or the way it ought to work. Otherwise, I see no reason for having a joins method, other than to help us build the query. So if any Rails gurus out there know how to use joins to populate models related to those tables, PLEASE LET ME (US) KNOW!

Anyway, I hope this helps you move along for now.

---

UPDATE: So I think I just figured it out. I stumbled across this blog post. As it turns out, when using the joins method of the query interface, Rails will actually add the columns you selected from the joined tables as attribute methods of the model being joined against.

Using the same example above, I can actually access the category_name of each post by simply calling post.category_name. #$%! Unbelievably simple, but no documentation whatsoever on this!

Here it is once again:

query = Post.joins("LEFT JOIN categories ON post.category_id = categories.id")
query.select("posts.*, category.category_name")
posts = query.all

# access a post's category name
puts posts[0].category_name

# this is what I thought I would be able to do
# without Rails making another query to the database
puts posts[0].category.category_name

I hope this helps! :)