3

Wondering with the upgrade to Arel, if ActiveRecord yet supports an automated way to select in the columns from joined tables, without having to explicitly list them in a select clause.

Given I'm joining the users table to posts, Would like to be able to do something like this in a more succinct manner:

  scope :select_user,
    select("posts.*, " + User.columns.map{|c| 
      "users.#{c.name} user_#{c.name}"}.join(','))

So, I want to automatically select the fields from user so that I get say

Post.joins(:user).select_user.first.user_name
aceofspades
  • 7,568
  • 1
  • 35
  • 48

2 Answers2

3

I think what you actually want here is to use includes if you want to access the association's attributes after the query is complete. So the same query would be

Post.includes(:user).first.user.name

This will result in two queries, one select * from users and one select * from posts where posts.user_id in (id1, id2, id3...) (assuming post belongs_to user) which may be a performance hit in a particular situation, but may be worth it from a syntax standpoint. In some cases it may even be faster Rails :include vs. :joins.

Arel will also intelligently use a join if you add a restriction operator to the query e.g. Post.includes(:user).where('users.name = "Bo Bob"') will do one query with a join and also enable you to use the associations on the resulting Post object.

Community
  • 1
  • 1
sguha
  • 2,057
  • 1
  • 22
  • 36
  • https://github.com/ntalbott/query_trace is very useful while developing to see what sql queries are generated by each rails command. – sguha May 24 '12 at 23:51
  • my question is actually how to select all the fields of post and user that joined, and some of the field name is the same. post.name and user.name – wizztjh May 25 '12 at 04:57
1

If I understand your question correctly, you can do it with 'joins':

class Post < ActiveRecord::Base
  belongs_to :user

  scope :select_user, joins(:user).where('users.name = "Mary"')
  ...
end

or with a param:

class Post < ActiveRecord::Base
  belongs_to :user

  scope :select_user, lambda {|user_name|
    joins(:user).where('users.name = ?', user_name)
  }
  ...
end
moritz
  • 25,477
  • 3
  • 41
  • 36
  • 1
    Thanks for your answer. I didn't show the join--the question is more about how to automatically select the fields from the joined table. – aceofspades May 26 '11 at 22:49