Laravelで人気ランキングサイトで順位の付け方。

Question

人気ランキングサイトを作成しています。（バージョンLaravel 6.x）
参考サイトを参考に、

FighterController

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

//Fighterモデルをインポート
use App\Fighter;
//Likeモデルをインポート
use App\Like;

（省略）

// DBをインポート
use DB;

class FighterController extends Controller
{
    //トップページ
    public function index()
    {
        // $fighters = Fighter::withCount('likes')
        // // いいねの多い順に並び替え
        // ->orderBy('likes_count', 'desc')
        // ->get();

        return view('fighters.index',[
            'title' => '格闘家人気ランキング',
            'fighters' => $fighters,
        ]);
    }

で、いいねの数が多い順に表示させることができました。
しかし、順位をつけようとしましたが、やり方が分かりませんでした。
連番をつけるという方法をしましたが、何か違うような。。。
どのようにして、一つ一つに順位を付けることができるのでしょうか。
各テーブル↓↓

usersテーブル

    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

likesテーブル

public function up()
    {
        Schema::create('likes', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('fighter_id');
            $table->timestamps();
            
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('fighter_id')->references('id')->on('fighters')->onDelete('cascade');
        });
    }

fightersテーブル
public function up()
    {
        Schema::create('fighters', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('fighter_name',100);
            $table->string('image',100);
            // 写真の引用元
            $table->string('image_quote',100);
            //SNSのアカウントリンク
            $table->string('account',100);
            $table->timestamps();
        });
    }

Comments

[hatena]

追記します。自分のコードだとこれでいいねの数が多い順に表示させることができました。
$fighters = Fighter::withCount('likes')->orderBy('likes_count', 'desc')->get();

Answer 1

MySQLでランキングに順位番号を取得する簡単な方法
MySQL 5.7 でクエリだけでランキングを実現する方法
このあたりが参考になると思います。

MySQL 8以上であれば、Window関数使えるので、以下のSQLでも取得できる

select
    fighters.id,
    count(likes.user_id),
    rank() over (order by count(likes.user_id) desc)
from fighters
left join likes on fighters.id = likes.fighter_id
group by fighters.id
order by count(likes.user_id) desc

$fighters = Fighter::query()
    ->selectRaw('fighters.*')
    ->addSelect(DB::raw('count(likes.user_id) as likes_count'))
    ->addSelect(DB::raw('rank() over (order by count(likes.user_id) desc) as rank'))
    ->leftJoin('likes', 'fighters.id', '=', 'likes.fighter_id')
    ->groupBy('fighters.id')
    ->orderByRaw('count(likes.user_id) desc')
    ->get();

Comments

[hatena]

回答ありがとうございます。
自分まだまだなので、本当に無知なのです。
ご迷惑をお掛けします。
申し訳ございません。
参考サイトを拝見しました。
sql文は、
クエリビルダに直すかんじですか？
それとも、参考サイトのsql分を直接コントローラに書いて、反映させるかんじですか？

[phper.k]

Laravel を使っているんですから、Eloquent や クエリビルだで書くのが定石ですね。

[hatena]

返答ありがとうございます。言い忘れていました。 バージョンは、Laravel 6.x を使っております。

[hatena]

丁寧な回答ありがとうございます。
追記しました通り、productsテーブルを自分ではfightersテーブルで作成しています。
phper.kさんが回答して下さったのを参考にして、一部変更しました。
$fighters = Fighter::query()
->selectRaw('fighters.*')
->addSelect(DB::raw('count(likes.user_id) as likes_count'))
->addSelect(DB::raw('rank() over (order by count(likes.user_id) desc) as rank'))
->leftJoin('likes', 'users.id', '=', 'likes.user_id')
->groupBy('fighters.id')
->orderByRaw('count(likes.user_id) desc')
->get();
しかし、自分のやり方が悪いのかdd($fighters)で確認したところ、エラーが表示されてしまいました。

以下、エラー
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.id' in 'on clause' (SQL: select fighters.*, count(likes.user_id) as likes_count, rank() over (order by count(likes.user_id) desc) as rank from `fighters` left join `likes` on `users`.`id` = `likes`.`user_id` group by `fighters`.`id` order by count(likes.user_id) desc)

[phper.k]

エラーメッセージは読んでますか？
読んだけど理解できないのですか？

[hatena]

読みましたが、理解ができず…。
すみません。もう少し考えます。

[phper.k]

`Column not found: 1054 Unknown column 'users.id' in 'on clause' `
usersテーブルのidカラムがないと言っています。
`in 'on clause' ` なのだから、
`->leftJoin('likes', 'users.id', '=', 'likes.user_id')` この部分ですね
回答例はあくまで、当初の質問から分かる範囲と推測で書いているものですし、手元の状態と異なるなら「ご自身で環境に合わせて修正対応できる」ことを保証してもらわないといけません。
対応することが難しいなら、手元の状態をそのまま嘘やごまかしなく提示してください。

[hatena]

申し訳ございません。質問の内容を修正いたしました。

usersテーブルにidカラムが無いというエラーということだったので、テーブルを調べたところ、
カラムが存在しているようですが、勘違いでしたらすみません。

[phper.k]

回答修正済み

[hatena]

SQLSTATE[42000]: Syntax error or access violation: 1055 'fighter_popularity_ranking.fighters.fighter_name' isn't in GROUP BY (SQL: select fighters.*, count(likes.user_id) as likes_count, rank() over (order by count(likes.user_id) desc) as rank from `fighters` left join `likes` on `fighters`.`id` = `likes`.`fighter_id` group by `fighters`.`id` order by count(likes.user_id) desc)
というエラーが出たため、調べました。
以下のサイトで解決済。
https://qol-kk.com/wp2/blog/2019/03/01/post-1125/
大変お世話になりました。
とても、勉強になりました。ありがとうございます。またよろしくお願いいたします。。