Select distinct on does not work properly rails 4.2.5

Question

I have this two models:

class Comment < ActiveRecord::Base
  belongs_to :post
end

class Post < ActiveRecord::Base
  include PgSearch
  has_many :comments, dependent: destroy

  pg_search_scope :search_tsv, against: [:name], 
                    using: { tsearch: { 
                              tsvector_column: 'tsv', 
                              dictionary: 'english', 
                              prefix: true, any_word: true
                             } 
                            }

 scope :full_search, ->(q) {
   select('DISTINCT ON (comments.post_id) comments.*, posts.name as post_name, posts.id as post_id')
   .order('comments.post_id, comments.created_at DESC')
   .search_tsv(q)
   .joins('LEFT JOIN comments on comments.post_id = posts.id')
 }
end

As you see I try to implement a full text search on my Post model. I built TSVECTOR column and trigger to update it and all works like a charm if I only use search_tsv scope.

But I want for each post from search result to retrieve last added comment. For that I built full_search scope.

When I try to use this scope, the SQL query that is generated looks like:

> Post.full_search('My post name').to_sql
> SELECT DISTINCT ON (comments.post_id) comments.*, 
         posts.name as post_name, potst.id as post_id 
  FROM "posts" 
  INNER JOIN (SELECT "posts"."id" AS pg_search_id, (ts_rank(("posts"."tsv"), (to_tsquery('english', ''' ' || 'My' || ' ''' || ':*') || to_tsquery('english', ''' ' || 'post' || ' ''' || ':*') || to_tsquery('english', ''' ' || 'name' || ' ''' || ':*')), 0)) AS rank FROM "posts" WHERE ((("posts"."tsv") @@ (to_tsquery('english', ''' ' || 'My' || ' ''' || ':*') || to_tsquery('english', ''' ' || 'post' || ' ''' || ':*') || to_tsquery('english', ''' ' || 'name' || ' ''' || ':*'))))) AS pg_search_00699f600cf5a0ff57479a ON "posts"."id" = pg_search_00699f600cf5a0ff57479a.pg_search_id 
 LEFT JOIN comments on comments.post_id = posts.id  
 ORDER BY comments.post_id, comments.created_at DESC, pg_search_00699f600cf5a0ff57479a.rank DESC, "posts"."id" ASC

which looks fine for my. But when I try to use this scope in my search controller, results are strange... If I do:

posts = Post.full_search('My post name')
k = posts.first.comments
...

it generates this SQL query:

SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1  ORDER BY created_at DESC, rank DESC, id DESC  [["post_id", 7]]

and result is an empty array :(.

I could not understand what I and doing wrong [obviously I am doing something stupid here :( ].

Can you help me to fix this, please?

Paul A Jungwirth · Accepted Answer · 2016-03-19T04:24:58.620

This means that your query (the complicated one) found some Posts, and the first one had id 7. Then you asked for all the comments on that post, and it had no comments. That all seems in order. The SQL is different because it's a different question. It's not, "Find the posts with 'My post name'." It's "Find the comments for post 7."

EDIT: The reason things are getting confused is because you are SELECTing comments.*, and ActiveRecord uses that to instantiate Post objects (not Comment objects). So you are getting comments.id of 7 and ActiveRecord thinks that's the Post's id. Also there is no point to selecting post_id and post_name. This should keep ActiveRecord from getting mixed up:

   select('DISTINCT ON (comments.post_id) posts.*')

But also: since you are only searching on the name of the post, I'm not sure why you're joining to comments at all. . . .

Select distinct on does not work properly rails 4.2.5

1 Answers1