Rails(SQL) いいね順に投稿リソースをソートしたい

Question

投稿リソース(Diary)にはいいね(LIKE)リソースをたくさん持っている状態で、
Diary.rbにクラスメソッドを作り、外部テーブルであるLike.rbを所持する件数によって、Diaryリソースを並び替えるメソッドを組んでいますがSQLの知識が乏しいためうまくいきません。
どのように構成すればいいのかご教授いただけたら嬉しいです。

なお実装しようとしているのは並び替え機能で投稿順・投稿の古い順・いいねの多い順・いいねの少ない順を
termにcontrollerの値を埋め込んでcase文でDiaryリソースを指定した順番で取得しようとしています。
4つあるうちのwhen 'likes'とwhen 'dislikes'は自分色々組み立てましたがうまくいきません。
よろしくお願いいたします。

def self.sort(term)
    case term
      when 'new'
        return all.order(created_at: :DESC)
      when 'old'
        return all.order(created_at: :ASC)
      when 'likes'
        return joins("LEFT OUTER JOIN likes ON likes.diary_id = diaries.id GROUP BY diaries.id ORDER BY count(likes.id) desc")
      when 'dislikes'
        return joins("LEFT OUTER JOIN likes ON likes.diary_id = diaries.id GROUP BY diaries.id ORDER BY count(diaries.id) asc")
    end
  end

教えていただいて実装しました

ありがとうございます。一部エラー内容が現れたみたいです

ActiveRecord::StatementInvalid - Mysql2::Error: Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'diary_app_development.likes.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT , COUNT() AS likes_count FROM diaries LEFT OUTER JOIN likes ON likes.diary_id = diaries.id GROUP BY diaries.id ORDER BY likes_count ASC LIMIT 5 OFFSET 0:

Answer 1

def self.sort(term)
  case term
    when 'likes'
      self.order_by_likes_count('desc')
    when 'dislikes'
      self.order_by_likes_count('asc')
  end
end

def self.order_by_likes_count(order)
  select("*, COUNT(*) AS likes_count")
    .left_joins(:likes)
    .group("diaries.id")
    .order(:likes_count => order)
end

left_joins ですべての diary を含めるようにします。
ただの joins だと likes が 0 の diary は含まれません。

select に COUNT(*) AS likes_count を追加して diaries.id の数を集計します。
この diaries.id の数は likes の数なのでこれでソートします。

これは都度集計するので遅くなる可能性があります。
rails には counter_cache という機能が用意されていて、
それを使うと関連の数が増減するとそれに合わせてカラムの値も変化します。
数をカラムに記録しているので以下のように書けるようになります。

Diary.order(:likes_count)

Comments

[JuniorSirius]

ありがとうございます。
left_joins,joinsメソッドの挙動仕組みが納得できました。
ソートの仕組みもわかりやすいですし、保守性も不安だったので機能面でも教えていただけて、助かりました。
しかしエラーが出てしまい、そちらを追記致しましたので、もし宜しければ教えていただけると幸いです。

[neko_daisuki]

MySQL のバージョンによってエラーになるようですね。
https://qiita.com/naota7118/items/5872a3033162b979832f

select のところを select("*, COUNT(users.id) AS likes_count") にするとどうでしょうか？

[JuniorSirius]

ご指摘いただき、ありがとうございます。
select("*, COUNT(users.id) AS likes_count")
.left_joins(:likes)
としたらMysql2::Error: Unknown column 'users.id' in 'field list':
と出たので
select("*, COUNT(diaries.id) AS likes_count")
.left_joins(:likes)
としたら以下のようになってしまいました。
ActiveRecord::StatementInvalid - Mysql2::Error: Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'diary_app_development.likes.id' which is not functionally dependent on columns in GROUP BY clause;

[neko_daisuki]

解決されたのでしょうか？

[JuniorSirius]

すみません、まだ上のコードエラーの通り解決していませんがロジックとしては、参考にさせていただいたのでとりあえずベストアンサーにさせて頂いた次第でございます・・・

[neko_daisuki]

分かりました。こちらで環境を用意できないので動作確認できないのですが、以下のコードでどうでしょうか

ordered_ids = select("COUNT(users.id) AS likes_count").left_joins(:likes).group("users.id").order(:likes_count => order).ids

where(id: ordered_ids).order("FIELD(id, #{ordered_ids.join(",")})")

[neko_daisuki]

ごめんなさい、users.id となってますが、diaries.id です。

[JuniorSirius]

ご協力ありがとうございます。今度は事前にASで定義しているのに、カラムが存在しないと言われました。
ActiveRecord::StatementInvalid - Mysql2::Error: Unknown column 'likes_count' in 'order clause': SELECT `diaries`.`id` FROM `diaries` LEFT OUTER JOIN `likes` ON `likes`.`diary_id` = `diaries`.`id` GROUP BY diaries.id ORDER BY `likes_count` ASC:
app/models/diary.rb:67:in `order_by_likes_count'
app/models/diary.rb:60:in `sort'

self.order_by_likes_countの実装は以下のようにいたしました。
def self.order_by_likes_count(order)
ordered_ids = select("COUNT(diaries.id) AS likes_count")
.left_joins(:likes).group("diaries.id")
.order(:likes_count => order).ids
.where(id: ordered_ids)
.order("FIELD(id, #{ordered_ids.join(",")})")
end

[JuniorSirius]

また、付け足して実装できたらと思って着手してみたら、思ったよりSQLなどの知識の必要性を感じたので、
将来的に学習をして、neko_daisuki様から頂いたご回答を仰ぎながら着手しようかと思います。
なので急ぎではないので、とりあえずは動かなくてもこの機能は最悪、今は見送ろうかと・・

[neko_daisuki]

ids とすると select が上書きされてしまうのですね。
それで COUNT のところがなくなってしまったのだと思います。

以下は時間ができましたらお試しください。
---
ordered_ids = select("diaries.id, COUNT(likes.id) AS likes_count").left_joins(:likes).group("diaries.id").order(:likes_count => order).map(&:id)

where(id: ordered_ids).order("FIELD(id, #{ordered_ids.join(",")})")
---
diaries.id だと likes の数 が　0 と 1 を区別できなかったため
COUNT に指定するカラムを diaries.id から likes.id に変更しました。
また、ふたつのクエリから成り立っているので where を . で繋がないでください。

以下の記事を参考にしました
https://chaika.hatenablog.com/entry/2019/02/19/120000

要は group に指定したカラム(diaries.id)以外は select に指定できない。
それは * を含み、COUNT や MAX などの集約関数は除く、ということだと思います。

ここで試しました(ordered_ids の部分のみ)
https://paiza.io/projects/owm5Jr3McNes09NUmZNs2w?language=mysql

[JuniorSirius]

心強いご回答でありがたい限りです・・・。
すみません！繋いでしまったのでつながないように気をつけます。

記事も記載していただいてありがとうございます。
じっくりと拝見いたしまして自分でしっかり理解しながら組み立てられるように、ご回答の内容を参考に実装してみようかと思います。
また進捗が見られましたら、ご報告いたしたいと思います!