Rails 5+PostgreSQL：如何基于关联表字段排序has_many关联列表？

Hey there! Let's break down how to get your commented_users sorted by the Comment's created_at descending instead of the default User fields. Since you're new to PostgreSQL, I'll explain each part clearly so you understand what's happening.

Step 1: Understand the Association & Sorting Requirement #

Right now, your default commented_users association pulls users through comments, but it sorts by the User model's default (usually id). To sort by the comment's creation time, we need to explicitly tell Rails to use the comments.created_at field for ordering.

Option 1: Define a Sorted Association in the Review Model #

This is a clean approach because you can reuse the sorted association anywhere in your app. Update your Review model like this:

class Review < ApplicationRecord
  has_many :comments
  # Add distinct to avoid duplicate users (if a user commented multiple times on the same review)
  has_many :commented_users, 
           through: :comments, 
           source: :user,
           -> { distinct.order(comments: { created_at: :desc }) }
end

What's happening here: #

• distinct: Ensures a user only appears once in the list, even if they left multiple comments on the same review.
• order(comments: { created_at: :desc }): Tells PostgreSQL to sort users based on the comments table's created_at field (not the user's own created_at), in descending order (newest comments first).

Now in your controller, you can just call:

review = Review.find_by_id(params[:id])
sorted_users = review.commented_users # Already sorted correctly!

Option 2: Manually Build the Query in the Controller #

If you prefer not to modify the model association, you can construct the query directly in your controller:

review = Review.find_by_id(params[:id])
sorted_users = User.joins(:comments)
                   .where(comments: { review_id: review.id })
                   .distinct
                   .order(comments: { created_at: :desc })

Breakdown of this query: #

• joins(:comments): Links the User table to the Comments table, so we can access comment-specific fields.
• where(comments: { review_id: review.id }): Filters users to only those who commented on the specific review.
• distinct: Removes duplicate user entries (critical if a user left multiple comments).
• order(comments: { created_at: :desc }): Explicitly sorts by the comment's creation time in descending order.

Why This Works with PostgreSQL #

PostgreSQL is strict about ambiguous column names—since both users and comments have a created_at field, we have to specify which table's created_at we want to sort by. Using the hash syntax comments: { created_at: :desc } (or raw SQL like "comments.created_at DESC") eliminates that ambiguity.

Test It Out! #

Open your Rails console (rails c) to verify the results:

review = Review.first
# Check the sorted user list
review.commented_users.pluck(:email, :created_at)
# Compare with the comment order to confirm
review.comments.order(created_at: :desc).pluck(:user_id, :created_at)

You should see the users ordered from the one who left the most recent comment to the oldest.

内容的提问来源于stack exchange，提问作者Shruthi R