SQL Joins in Rails

Question

Trying to work with an existing database in rails. The below works just great in MySQL Console:

select title,blog_name from exp_weblog_titles JOIN exp_weblogs ON exp_weblog_titles.weblog_id = exp_weblogs.weblog_id LIMIT 1;

+------------+---------------+
| title      | blog_name     |
+------------+---------------+
| What We Do | chicago_pages | 
+------------+---------------+
1 row in set (0.00 sec)

However in the rails console, this is what I get:

>> @titles = Title.find_by_sql("select title,blog_name from exp_weblog_titles JOIN exp_weblogs ON exp_weblog_titles.weblog_id = exp_weblogs.weblog_id LIMIT 1")
=> [#<Title title: "What We Do">]

I've seen allusions to the fact that for some reason Rails will only display columns from the first table in console mode without a bit of trickery. Can anyone tell me how to access joined attributes?

Answer 1

I am not completely sure of what you mean with joined attributes. However, I guess what you need is this:

class Title < ActiveRecord::Base
   belongs_to :weblog
end

class Weblogs < ActiveRecord::Base
   has_many :titles
end

You can get the title attributes of a weblog doing the following

@weblog = Weblog.find(...) 
@webglog.title.attributes

And the other hand

@titles = Title.find_all
@titles[0].weblog.attributes

Hope that helps

Answer 2

If you don't want to restructure the existing tables, it's possible to make Rails conform.

With such AR models:

class Title < ActiveRecord::Base
  set_table_name "exp_weblog_titles" 

  belongs_to :weblog, :foreign_key => 'weblog_id' # foreign_key not even needed here
end

class Weblog < ActiveRecord::Base
  set_table_name "exp_weblogs"
  set_primary_key "weblog_id"

  has_one :title, :foreign_key => 'weblog_id' # or here
end

this should work:

Weblog.all.each {|w| puts "#{w.title.title} #{w.blog_name}"}

Or use Title.connection.select_rows('SELECT statement here'), this will return an array of rows, but then I don't get the point of using Rails.