has_one association not working with includes

Question

I've been trying to figure out some odd behavior when combining a has_one association and includes.

class Post < ApplicationRecord
  has_many :comments

  has_one :latest_comment, -> { order('comments.id DESC').limit(1) }, class_name: 'Comment'
end

class Comment < ApplicationRecord
  belongs_to :post
end

To test this I created two posts with two comments each. Here are some rails console commands that show the odd behavior. When we use includes then it ignores the order of the latest_comment association.

posts = Post.includes(:latest_comment).references(:latest_comment)
posts.map {|p| p.latest_comment.id}
=> [1, 3]

posts.map {|p| p.comments.last.id}
=> [2, 4]

I would expect these commands to have the same output. posts.map {|p| p.latest_comment.id} should return [2, 4]. I can't use the second command because of n+1 query problems.

If you call the latest comment individually (similar to comments.last above) then things work as expected.

[Post.first.latest_comment.id, Post.last.latest_comment.id]
 => [2, 4]

If you have another way of achieving this behavior I'd welcome the input. This one is baffling me.

Answer 1

I think the cleanest way to make this work with PostgreSQL is to use a database view to back your has_one :latest_comment association. A database view is, more or less, a named query that acts like a read-only table.

There are three broad choices here:

1. Use lots of queries: one to get the posts and then one for each post to get its latest comment.
2. Denormalize the latest comment into the post or its own table.
3. Use a window function to peel off the latest comments from the comments table.

(1) is what we're trying to avoid. (2) tends to lead to a cascade of over-complications and bugs. (3) is nice because it lets the database do what it does well (manage and query data) but ActiveRecord has a limited understanding of SQL so a little extra machinery is needed to make it behave.

We can use the row_number window function to find the latest comment per-post:

select *
from (
  select comments.*,
         row_number() over (partition by post_id order by created_at desc) as rn
  from comments
) dt
where dt.rn = 1

Play with the inner query in psql and you should see what row_number() is doing.

If we wrap that query in a latest_comments view and stick a LatestComment model in front of it, you can has_one :latest_comment and things will work. Of course, it isn't quite that easy:

1. ActiveRecord doesn't understand views in migrations so you can try to use something like scenic or switch from schema.rb to structure.sql.

2. Create the view:

   class CreateLatestComments < ActiveRecord::Migration[5.2]
     def up
       connection.execute(%q(
         create view latest_comments (id, post_id, created_at, ...) as
         select id, post_id, created_at, ...
         from (
           select id, post_id, created_at, ...,
                  row_number() over (partition by post_id order by created_at desc) as rn
           from comments
         ) dt
         where dt. rn = 1
       ))
     end
     def down
       connection.execute('drop view latest_comments')
     end
   end
   

   That will look more like a normal Rails migration if you're using scenic. I don't know the structure of your comments table, hence all the ...s in there; you can use select * if you prefer and don't mind the stray rn column in your LatestComment. You might want to review your indexes on comments to make this query more efficient but you'd be doing that sooner or later anyway.

3. Create the model and don't forget to manually set the primary key or includes and references won't preload anything (but preload will):

   class LatestComment < ApplicationRecord
     self.primary_key = :id
     belongs_to :post
   end
   

4. Simplify your existing has_one to just:

   has_one :latest_comment
   

5. Maybe add a quick test to your test suite to make sure that Comment and LatestComment have the same columns. The view won't automatically update itself as the comments table changes but a simple test will serve as a reminder.

6. When someone complains about "logic in the database", tell them to take their dogma elsewhere as you have work to do.

---

Just so it doesn't get lost in the comments, your main problem is that you're abusing the scope argument in the has_one association. When you say something like this:

Post.includes(:latest_comment).references(:latest_comment)

the scope argument to has_one ends up in the join condition of the LEFT JOIN that includes and references add to the query. ORDER BY doesn't make sense in a join condition so ActiveRecord doesn't include it and your association falls apart. You can't make the scope instance-dependent (i.e. ->(post) { some_query_with_post_in_a_where... }) to get a WHERE clause into the join condition, then ActiveRecord will give you an ArgumentError because ActiveRecord doesn't know how to use an instance-dependent scope with includes and references.