interprism's blog

インタープリズム株式会社の開発者ブログです。

【Laravel】select句にsubQueryを構築したい

laravel

【Laravel】select句にsubQueryを構築したい

初めまして、だーさんです。
Laravel(PHP)とMySQLを使用してWebサイトを作成する仕事をしています。

Laravelのクエリビルダーを使用してMySQLのコードを書いていてちょっと行き詰まったので記事を書いてみようかなと思いました。 探してもうまい方法が見つからなかったので誰かの助けになればいいなと思います。 （スマートなやり方を知っている方がいたらぜひ教えてほしいですっっ）

環境

mac
PHP: 7.3.33
Laravel Framework: 5.7.28
MySQL: 5.7.36

やりたいこと

Laravelのクエリビルダでselect句内にサブクエリを書きたい。 MySQLでは以下のような書き方になります。

SELECT
    t.team_id,
    (SELECT
         COUNT(DISTINCT a.member_id) AS member_count
     FROM
         table_member AS a
     WHERE
         a.team_id = t.team_id
     GROUP BY a.team_id
    ) AS member_count
FROM
    table_team AS t

上記は業務で使用しているコードを基に必要な部分だけ抜き出したものです。
従って、サブクエリにしなくても目的を達成できるコードになってしまいましたが、趣旨がずれてしまうので今回はご了承ください。(^^;)

抽出したいデータはチーム(team)に対するそれぞれのメンバー数(member)です。
今回行き詰まったポイントとなったのは、基盤となるFROMのtable_teamのIDカラムとサブクエリ内のtable_memberのIDカラムを結合するところでした。

a.team_id = t.team_id

成功したコード

結論として以下のコードで目的のコードを構築することになりました。

DB::table('table_team AS t')
    ->select([
        't.team_id'
        , DB::raw("(
            SELECT
                COUNT(DISTINCT a.member_id) AS member_count
            FROM
                table_member AS a
            WHERE
                a.team_id = t.team_id
            GROUP BY a.team_id) AS member_count")
    ])
    ->get();

select()にDB::raw()で生のMySQL文を埋め込むやり方です。

残念なポイントはサブクエリ部分が生のMySQL文になってしまったことです。 DB::raw()を使用すると部分的に生のMySQL文を埋め込むことができます。 これを使用し、select句内に無理やり埋め込む形で構築しました。

本当はサブクエリ内もクエリビルダーで構築したかったのですが、うまくいかず上記の形で落ち着きました。 以下でここに辿り着くまでの失敗例を載せて行きます。 お時間ある方はお付き合いください。m(_ _)m

失敗例1(構文エラー)

select句内にMySQL文入れ込むので初めは単純に以下のように無名関数で入れてみました。

DB::table('table_team AS t')
    ->select([
        't.team_id'
        , function (Builder $query) {
            $query->select([
                    DB::raw('COUNT(DISTINCT a.member_id) AS member_count')
                ])
                ->from('table_member AS a')
                ->where('a.team_id', '=', 't.team_id')
                ->groupBy(['a.team_id'])
                ->get();
        }
    ])
    ->get();

こちらは単純に構文エラー。 select()はこのような関数の形を許容していないらしいです。

stripos() expects parameter 1 to be string, object given

失敗例2(カラムが見つからない)

調べてみるとLaravelにselectSub()というサブクエリ用の関数が存在したので使用してみました。

DB::table('table_team AS t')
    ->select([
        't.team_id'
    ])
    ->selectSub(
        function (Builder $query) {
            $query->select([
                    DB::raw('COUNT(DISTINCT a.member_id) AS member_count')
                ])
                ->from('table_member AS a')
                ->where('a.team_id', '=', 't.team_id')
                ->groupBy(['a.team_id'])
                ->get();
        }, 'member_count')
    ->get();

こちらは取得データが何やらおかしい。。。むむ。
構築されるMySQL文を見てみると以下の形に、

SELECT
    t.team_id,
    (SELECT
         COUNT(DISTINCT a.member_id) AS member_count
     FROM
         table_member AS a
     WHERE
         a.team_id = 't.team_id'  #<--- 文字列になっている！！
     GROUP BY a.team_id
    ) AS member_count
FROM
    table_team AS t

where()の使用の仕方が間違っており、't.team_id'という文字列にマッチするものだけ検索してしまっていました。 カラム名を使用できるように以下に書き換え！

DB::table('table_team AS t')
    ->select([
        't.team_id'
    ])
    ->selectSub(
        function (Builder $query) {
            $query->select([
                    DB::raw('COUNT(DISTINCT a.member_id) AS member_count')
                ])
                ->from('table_member AS a')
                ->where('a.team_id', '=', DB::raw('t.team_id'))
                ->groupBy(['a.team_id'])
                ->get();
        }, 'member_count')
    ->get();

こちらはちゃんとカラム名で指定できました！ ただ、「t.team_idというカラムは存在しません。」というエラーが出ました。 どうやら、このselectSub()はこれのみで完結するSQL文しか書けない模様。

まとめ

select句内にサブクエリを構築するのに、selectSub()を使用するとjoin句などで使用しているカラムを指定することができません。 従ってDB::raw()で生のMySQL文をselect()に埋め込む方法で構築することになります。

クエリビルダーで完結できないかと結構調べたのですが、結論としては「なんとも惜しい」形での収束となってしまいました。 私自身がそうなのですが、できそうでできないと諦めきれなくなってしまうので、 この記事を見て「できないんだー」と思えるところに落ち着けるタイミングになればいいなと思います。 (理想はこういうSQLにLaravelが対応してくれることですが笑)

最後までお付き合いいただきありがとうございました。 また、どこかで会えますように。

インタープリズムのページ

PAGE TOP