rails Active record selection

Q: all post and related comments selection in one query, loading comments relation into p.comments, but select only title from comments

Post

#db
create_table :posts do |t|
  t.string :title
  t.text :text

  t.timestamps
end

#modlel
class Post < ActiveRecord::Base
  has_many :comments
end

Comment

#db
create_table :comments do |t|
  t.string :title
  t.text :text
  t.integer :post_id

  t.timestamps
end

#model
class Comment < ActiveRecord::Base
  belongs_to :post
end

What I need: Select all post and related comments in one query and load comments relation into p.comments, but select only title from comments:

posts = Post.includes(:comments).select("posts.*, comments.title") 
posts.each do |p|
    puts(p.title)
    puts(p.text)
    p.comments.each do |c| 
        puts(c.title)
    end
end

If first part is done by .includes:

posts = Post.includes(:comments).select('posts.*, comments.title')
posts.first.comments.loaded? # true

=> SELECT posts.id AS t0_r0, posts.title AS t0_r1, posts.text AS t0_r2, posts.created_at AS t0_r3, posts.updated_at AS t0_r4, comments.id AS t1_r0, comments.title AS t1_r1, comments.text AS t1_r2, comments.post_id AS t1_r3, comments.created_at AS t1_r4, comments.updated_at AS t1_r5 FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id

its completely ignores select.

Ok, lets try joins:

Post.joins(:comments).select('posts.*, comments.title')

=> SELECT posts.*, comments.title FROM posts INNER JOIN comments ON comments.post_id = posts.id

better, but we need table alias -- Rails doesn't handle relation (why?)

posts = Post.joins(:comments).select('posts.*, comments.title as comments_title').first

=> SELECT posts.*, comments.title as comments_title FROM posts INNER JOIN comments ON comments.post_id = posts.id

posts.first.comments.loaded? # false
posts.first.comments_title # "Comment 1.1"

Ok, lets try ARel

p = Post.arel_table
c = Comment.arel_table
posts = Post.find_by_sql(
    p.join(c).on(c[:post_id].eq(p[:id])) \
     .project(p[:id],p[:title],c[:id],c[:title]).to_sql
)

=> SELECT posts.id, posts.title, comments.id, comments.title FROM posts INNER JOIN comments ON comments.post_id = posts.id

same story -- ActiveRecord doesn't handle relation.

any suggestions?

UPD

1. if this question hasn't direct solution, maybe there is a way to use some AR methods to convert resulting query. Because, AR uses ARel, then call find_by_sql on it and then do some magic, that I cant find when and were its performed, and viola: generated aliases and loading associations.

2. Maybe there is solution for one-to-one relation?

   posts = P开发者_如何转开发ost.select('posts.id,posts.title,comments.id,comments.title').includes(:comment) posts.first.comment.loaded? #true

=> SELECT posts.id AS t0_r0, posts.title AS t0_r1, posts.text AS t0_r2, posts.created_at AS t0_r3, posts.updated_at AS t0_r4, comments.id AS t1_r0, comments.title AS t1_r1, comments.text AS t1_r2, comments.post_id AS t1_r3, comments.created_at AS t1_r4, comments.updated_at AS t1_r5 FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id

I'm afraid I don't have any suggestion because all the scenarios you described is working as intended.

• :includes => eager loading
• :joins => NOT eager loading

So if you want comments to be loaded directly you must use includes, joins will never work.

Next thing, eager loading works because of the certain select syntax used in the SQL query. In other words, your specified select will be ignored when used in conjunction with includes and all columns will be loaded by default.

I think the most recent try to add the functionality you are looking for is described in this ticket where it is solved by adding an except parameter in the call to includes. It does not seem to have gotten any response yet though.

Personally I would go for the first example you specified with includes but skip the select and live with the fact that the comment.text is loaded. Better then any other scenario I can think of.

You can also use a raw SQL query with active record simply by doing the following:

post_comment = Post.find_by_sql("SELECT p.*, c.title AS comment_title FROM posts p JOIN comments c ON p.id=c.post_id").first
post_comment.comment_title

A little hacky but it seems to work. ActiveRecord also exposes the connection attribute for you to do even more complex queries. http://apidock.com/rails/ActiveRecord/Base/connection

one of the solutions is switch to DataMapper instead of ActiveRecort. It is much better orm then AR for me. Use DataMapper instead of ActiveRecord