京都のシステム開発・ホームページ制作会社　株式会社Nextat（ネクスタット）

MySQLで連番を付与する方法です。
結論からいうと、
「FROM句で変数を初期化→SELECT句に『変数＝変数+1』を記載して連番値を取得」
とすると連番を実装できます。

ソースとしては以下の様になります。

SELECT
  (@num := @num + 1) AS serial_number,
  target_table.*
FROM
  target_table,
  (select @num := 0) dummy_table
 ;

MySQLで連番を付与するのはさほど苦労することはないと思います。
問題はLaravelでどうやって連番を実装するかということです。

Laravelの何が問題かというと、QueryBuilderを経由する関係で、基本的に「Inner Join」と「Left Join」しか使えないということです。
そのため、「FROM <<対象テーブル>>,  (select @num := 0) dummy_table」のようなソースを直接記述することができません。

「Inner Join」や「Left Join」しか使えないため、「FROM <<対象テーブル>>,  (select @num := 0) dummy_table」のようなSQLを実現するためには、「基点となるテーブルを作るタイミング（\DB::table()メソッドを呼び出すタイミング）」で直接「FROM <<対象テーブル>>,  (select @num := 0) dummy_table」のような記述を行う必要があります。
ソースとしては以下のようになります。

        $subQuery = \DB::table('test_tables');
        $subQuery->select([
            'test_tables.*',
        ]);
        
        $subQuerySql = $subQuery->toSql();
        $convertQuery = \DB::table(\DB::raw("({$subQuerySql}) sub_table, (select @num := 0) dummy_table"));
        $convertQuery->select([
            'sub_table.*',
            \DB::raw('(@num := @num + 1) AS serial_number'),
        ]);
        $rows = $convertQuery->get();

さて、これで連番を利用する方法がわかったので良かった良かった、と行きたいところですが、まだ完成ではありません。
何故なら、Laravelを利用する場合、N+1問題を解決する関係で以下のような形式でデータを取得するのが一般的だからです。

        $query = TestTable::with([
            'test_tables2',
            'test_tables3',
        ]);
        $query->select([
            'test_tables.*',
        ]);
        $rows = $query->get();

さて、この場合、

• Laravelで連番を付与するためには、基点テーブルを作成するタイミングで変数を初期化する必要がある。（基点テーブルの記述後はLeftJoin、InnerJoinしか使えないため）
• N+1問題回避のために、「TestTable::query()」のようなモデルクラス経由の記述を行ってしまうと、「FROM <<対象テーブル>>,  (select @num := 0) dummy_table」のような記述を基点テーブル作成のタイミングで記述できない。

という問題があります。

そのため、モデルクラス経由でデータを取得し、かつ、連番を付与するためには少し工夫が必要になります。
私が以前に上記問題に直面した際は、以下のようにサブクエリを経由するようにすることで「モデルクラスを利用しつつ、連番を付与する」という目的を実現することにしました。

        // サブクエリのSQL作成
        $subQuery = \DB::table('test_tables');
        $subQuery->select([
            'test_tables.id',
        ])->distinct();
        $subQuery->join('test_tables2', 'test_tables.id', '=', 'test_tables2.parent_id');
        $subQuery->whereRaw("test_tables.price > 50000");
        $subQuery->whereRaw("test_tables2.distance < 50");

        // 連番付与
        $subQuery = $this->convertQueryWithSerialNumber($subQuery);
         
        // データ取得
        $query = TestTable::with([
            'test_tables2',
            'test_tables3',
        ]);
        $query->select([
            'test_tables.*',
                 \DB::raw('sub_test_tables.serial_number AS serial_number'),
        ]);
        $subQuerySql = $subQuery->toSql();
        $query->join(\DB::raw("({$subQuerySql}) AS sub_test_tables"), 'test_tables.id', '=', 'sub_test_tables.id');
        // 念のため連番の順番で並ぶようにしておく
        $query->orderBy('sub_test_tables.serial_number', "ASC");
        $rows = $query->get(); 

convertQueryWithSerialNumberメソッドの内容は以下になります。
（連番付与の部分は共通的な処理のため、メソッド化しています。）

    /**
     * クエリを連番付きクエリに変換します。
     * @param $subQuery
     * @return mixed
     */
    private function convertQueryWithSerialNumber($subQuery)
    {
        $subQuerySql = $subQuery->toSql();
        $convertQuery = \DB::table(\DB::raw("({$subQuerySql}) sub_table, (select @num := 0) dummy_table"));
        $convertQuery->select([
            'sub_table.*',
            \DB::raw('(@num := @num + 1) AS serial_number'),
        ]);
        return $convertQuery;
    }

さて、これで「Laravelで連番を付与する」という目的が達成できました。
Laravelでもっとスマートに書ける方法とかあれば良いのですけどね・・・