こんにちは❗フリーランス・コンサルタント＆エンジニアの九保すこひです。

X（Twitter）やってます！
📝 九保すこひ@フリーランスエンジニア｜累計300万PVのブログ運営中

さてさて、前回・前々回とLaravelの新バージョン6.0の新機能について紹介をしてきました。

今回はその３弾ですが、新機能というよりは以前からあった機能を強化したというイメージになります。ただ、これによって私が長年希望していたことを解決する運びとなりました😊✨

そして、その内容はというと、

データベースのサブクエリー

です。

もしかすると「サブクエリー」はあまり馴染みがないかもしれませんので、今回はサブクエリーとは何か？という根本的なところからご紹介していきたいと思います。（すでに知ってる方はサブクエリーの使い方まで読み飛ばしてください）

ぜひ皆さんのお役に立てると嬉しいです😊✨

開発環境： Laravel 6.0

1 サブクエリーとは
2 サブクエリーで何ができるのか
3 テスト環境をつくる
4 Laravel 6.0の新しいサブクエリーの使い方
- 4.1 SELECT句でサブクエリーを使う
- 4.2 ORDER BY句でサブクエリーを使う
5 おわりに

サブクエリーとは

まずは「サブ」ではない標準のクエリーから説明します。

「クエリー」とは大ざっぱに言うとSQL文のことで、以下のようなものになります。

SELECT * FROM users ORDER BY id DESC

最近の開発ではフレームワークを使うことが多くなったので、SQL文を直接書くことは少なくなりましたが、これが基本の「クエリー」になります。

そして、「サブクエリー」というのは、もっと詳しい条件指定をするもう１つクエリーです。

実際の例を見てみましょう。

SELECT * FROM users
    ORDER BY (SELECT id FROM posts where user_id = posts.id ORDER BY id desc limit 1) DESC

どうでしょう。

少し複雑に見えるかもしれませんが、実はこれ太字の括弧の部分がひとつのクエリーになっているだけで、さっきの標準クエリーと構造は全く同じです。

つまり、サブクエリーとは「クエリーの中にあるクエリー」といってもいいでしょう。

SELECT * FROM users
    ORDER BY (ここがサブクエリー) DESC

サブクエリーで何ができるのか

実はこれまででもLaravelでは以下のメソッドを使えばサブクエリーを実行することができました。

selectRaw()
whereRaw()
OrderByRaw();

ただし、この場合は部分的とはいえ、やはり自分でSQL文を書く必要があるので複雑なものを記述すると可読性が悪くなってました。

では、サブクエリーを使えば何ができるのかというと、例えば次のような条件でデータ取得できるようになります。

ユーザーが受けたテストの最高点も追加してデータ取得
一番はじめに注文した日時も一緒にデータ取得
最新記事の日時でユーザーを並べ替え

つまり、リレーションシップ先との連携をより細かく指定することができます。

テスト環境をつくる

前提として

前提としてLaravelのログイン機能がインストールされているものとします。

※ まだインストールしていない人は、Laravel6.0でログイン機能を使う方法を参考にしてください。やり方が変更になっています。

なお、usersテーブルとhasManyの関係になるテーブルは「exam_results」で、ここには各ユーザーが受けたテストの結果を保存します。

イメージは次のとおりです。

太郎さん：
・90点（受験日: 2019.09.01）
・73点（受験日: 2019.08.15）

次郎さん：
・30点（受験日: 2019.09.03）
・63点（受験日: 2019.08.07）
・72点（受験日: 2019.08.01）

三郎さん：
・98点（受験日: 2019.08.27）

モデルとマイグレーションをつくる

では、まずはテスト結果を管理するモデルとマイグレーションをつくりましょう。以下のコマンドを実行してください。

php artisan make:model ExamResult -m

すると、database/migrations/****_**_**_******_create_exam_results_table.phpというファイルが作成されるので中身を以下のように変更します。

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateExamResultsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('exam_results', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->integer('score');
            $table->timestamps();

            $table->foreign('user_id')
                ->references('id')->on('users')
                ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('exam_results');
    }
}

Seederをつくる

続いて、テストデータをSeederで作成します。
以下のコマンドを実行してください。

php artisan make:seeder ExamResultsTableSeeder

database/seeds/ExamResultsTableSeeder.phpが作成されるので、中身を以下のように変更します。

<?php

use Illuminate\Database\Seeder;

class ExamResultsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $user_ids = \App\User::pluck('id');

        for($i = 0 ; $i < 100 ; $i++) {

            $exam_result = new \App\ExamResult();
            $exam_result->user_id = $user_ids->random();
            $exam_result->score = rand(30, 100);
            $exam_result->save();

        }
    }
}

Seederが作成できたら、database/seeds/DatabaseSeeder.phpに忘れず登録しておきましょう。

<?php

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
         // 省略

         $this->call(ExamResultsTableSeeder::class);
    }
}

では、この状態でSeederを実行します。

php artisan migrate:fresh --seed

※ 今回はマイグレーションをはじめからやり直す形にしていますがSeederの実行だけの場合はphp artisan db:seedを使ってください。

Seederを実行すると以下のようになります。

Laravel 6.0の新しいサブクエリーの使い方

では、前置きが長くなりましたがここからがメインの新しいサブクエリーの使い方です。

SELECT句でサブクエリーを使う

例えば、usersのデータに加えて「そのユーザーが取得した最高点」も一緒にデータ取得する例です。

$users = \App\User::addSelect(['max_score' => \App\ExamResult::select('score')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('score', 'desc')
    ->take(1)
])->get();

dump($users->toArray());

これを実行した結果はこうになります。

元々usersにはmax_scoreというフィールドはありませんがサブクエリーで取得ができました。

なお、whereColumn()で２つのテーブルを結合していることに注目してください。これによって「各ユーザーの得点データ」を取得することができるようになります。

whereColumn('user_id', 'users.id')

また、基本的に「クエリー」の指定なので、first()などのメソッドを記述していないことも覚えておいてください。

ORDER BY句でサブクエリーを使う

続いては結合先のデータによって並べ替えをする方法です。

$users = \App\User::orderByDesc(
    \App\ExamResult::select('score')
        ->whereColumn('user_id', 'users.id')
        ->orderBy('score', 'desc')
        ->take(1)
)->get();

dump($users->toArray());

実行した結果は以下のとおりです（ただしこの場合、実際にはわかりやすいように最高点も追加しています）