【Laravel】クエリビルダ：サブクエリのJOIN〜条件付きサブクエリにはmergeBindingsを使おう〜

はじめに

Laravelのクエリビルダーで条件付きサブクエリをDB::raw()を使用してjoinをした時にInvalid parameter numberとなったので、対処方法をまとめます。

Where句のバインド値をマージしないとエラーになる

試しに以下のようなSQLをクエリビルダ ーのDB::raw()を使用して実装していきたいと思います。

select * from (
    -- サブクエリ１
    select * from table1 where status = 'Success'
) as s1
left join (
　　　　　　　　-- サブクエリ２
    select * from table2 where status = 'New'
) as s2 on s1.id = s2.relid
where t.id = 1

定義したサブクエリをtoSql()でSQL文を生成して、DB::raw()を使用して組み立てていきたいと思います。

        //　サブクエリ１
        $subquery1 = \DB::table('table1')
            ->where('status', '=', 'Success');
        //　サブクエリ2
        $subquery2 = \DB::table('table2')
            ->where('status', '=', 'New');

        $query = \DB::table(\DB::raw("({$subquery1->toSql()}) as s1"))
            ->leftjoin(\DB::raw("({$subquery2->toSql()}) as s2"), function($join){
                $join->on('s1.id', '=','s2.relid');
            })
        ->where('t.id', '=', 1);
        $data = $query->get();

実際に実行をする以下のようにparameterのエラーが発生します。これはサブクエリをtoSql()でSQLを生成してもバインドした値は含まれないないのが原因です。

"SQLSTATE[HY093]: Invalid parameter number (SQL: select * from (select * from `table1` where `status` = 1) as s1 left join (select * from `table2` where `status` = ?) as s2 on `s1`.`id` = `s2`.`relid` where `s1`.`id` = ?) "

mergeBindingsを使用してバインド値を追加する

mergeBindings()を使ってバインド値をマージしていきます。
マージする順番も気をつける必要がありますので注意してください。

        $subquery1 = \DB::table('table1')
            ->where('status', '=', 'Success');
        $subquery2 = \DB::table('table2')
            ->where('status', '=', 'New');
        $query = \DB::table(\DB::raw("({$subquery1->toSql()}) as s1"))
            ->leftjoin(\DB::raw("({$subquery2->toSql()}) as s2"), function($join){
                $join->on('s1.id', '=','s2.relid');
            })
            ->mergeBindings($subquery1)
            ->mergeBindings($subquery2)
        ->where('t.id', '=', 1);
        $data = $query->get();