有条件地对查询结果排序

在这种情况下,您可以按两者的最大值进行排序。

Post.left_outer_joins(:comments).
order('GREATEST(comments.created_at, posts.created_at) DESC').
uniq

我刚用Ruby做了:

@posts = Post.where(category: params[:category])
@posts.each do |p|
  p.created_at = p.comments.count == 0 ? p.created_at : p.comments.last.created_at
end
@posts = @posts.sort_by { |k| k[:created_at] }.reverse
@posts = @posts.paginate(page: params[:page])

应用程序/模型/post.rb

class Post < ApplicationRecord
  has_many :comments
end

app/models/comment.rb应用程序

class Comment < ApplicationRecord
  belongs_to :post
end

数据库/架构.rb

ActiveRecord::Schema.define(version: 2018_09_20_081230) do

  create_table "comments", force: :cascade do |t|
    t.text "comment_content"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer "post_id"
  end

  create_table "posts", force: :cascade do |t|
    t.string "post_title"
    t.text "post_content"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

Post.includes(:comments).order('comments.created_at DESC')

测试环境:

• 红宝石5.2.1

更新

Post.joins('LEFT JOIN comments ON comments.post_id = posts.id').group('posts.id').order('COALESCE(MAX(comments.created_at), posts.created_at) DESC')

或者

Post.find_by_sql('SELECT posts.* FROM posts LEFT JOIN comments ON comments.post_id = posts.id GROUP BY posts.id ORDER BY COALESCE(MAX(comments.created_at), posts.created_at) DESC')