前提

投稿のPostモデルと、投稿を評価するEvaluationモデルがあり、
evaluationsテーブルにはpostsの外部キーpost_idがある

evaluationにはstatusというカラムがあり、enumで管理されており値は以下の通りである

models/evaluation.rb

  enum status: {
    bad: 0 ## 低評価
    good: 1 ## 高評価
  }

また、postは会員と管理者どちらでも投稿することができる
管理者が投稿する場合にはpost.admin_idに自分のidを入れる

簡略化したschema情報は以下の通りである

schema.rb

  create_table "posts", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin", force: :cascade do |t|
   t.bigint "admin_id"
  end

  create_table "evaluations", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin", force: :cascade do |t|
    t.bigint "post_id", null: false
    t.integer "status", default: 1, null: false
    t.index ["post_id"], name: "index_evaluations_on_post_id"
  end

要件

この場合に、評価なしの場合も含めてユーザーが投稿した投稿を高評価順に並び替えたい

コード

後の外部結合で使うため、条件付きのリレーションをpostモデルに作成しておく

model/post.rb

has_many :good_evaluations, lambda { where(status: :good) }, class_name: 'Evaluation'

コントローラーでは以下のように記述する

some_controller.rb

Post.left_joins(:good_evaluations)
     .where.not(admin_id: nil)
     .order('cnt_good desc')
     .group(:id)
     .select('posts.*, count(evaluations.post_id) ctn_good')

・ left_joins(:good_evaluation)で高評価されていないものも含めて取得したいためleft_joinsを使うがleft_joinsの中で条件を指定できないため、結合先を高評価のみの条件指定をしているリレーションを持ってくる
・　　group(:id)で外部結合されたevaluationの数を知りたいため投稿ごとにグルーピングする
・　　count(evaluations.post_id)で投稿ごとのレコード数を持ってくる

上記によって高評価のレコードがない投稿を含めて並び替えて取得ができる

実現できなかったクエリ

model/post.rb

has_many :evaluations, dependent: :destroy

some_controller.rb

Post.left_joins(:evaluations)
     .where.not(admin_id: nil)
     .where.not(evaluations: { status: :good })
     .order('cnt_good desc')
     .group(:id)
     .select('posts.*, count(evaluations.post_id) ctn_good')

これだと外部結合した後にwhereで絞り込んでいるのでinner_joinと同じような感じになってしまう