【Laravel】複合主キーのセットでWHERE INする

　LaravelにはEloquentというあるテーブルについてのSQLを扱える機能があります。これは基本的に主キーが1カラムだけのテーブルを前提としており次のような複合キーのテーブルを扱う時にしばしば苦労します。通常のLaravelでよくやる次のようなWHERE INを複合キーで行う方法を紹介します。この方法は少なくともMySQLとPostgreSQLで使えます。

User::query()->whereIn('user_id', [1, 2, 3])->get();

　まず元となるSQLが次です。単一のユーザーIDでなくサービスIDとユーザーIDの組み合わせを想定します。複数のサービスについてのユーザーを1テーブルにまとめ、サービスが違えばユーザーIDが重複してもいい、という感じです。

CREATE TABLE service_users  (
    service_id INT,
    user_id INT,
    username VARCHAR(255),
    PRIMARY KEY (service_id, user_id)
);
INSERT INTO service_users  (service_id, user_id, username) VALUES (1, 1, 'UserA');
INSERT INTO service_users  (service_id, user_id, username) VALUES (1, 2, 'UserB');
INSERT INTO service_users  (service_id, user_id, username) VALUES (1, 3, 'UserC');
INSERT INTO service_users  (service_id, user_id, username) VALUES (2, 1, 'UserD');
INSERT INTO service_users  (service_id, user_id, username) VALUES (2, 2, 'UserE');
INSERT INTO service_users  (service_id, user_id, username) VALUES (2, 3, 'UserF');
INSERT INTO service_users  (service_id, user_id, username) VALUES (3, 1, 'UserG');
INSERT INTO service_users  (service_id, user_id, username) VALUES (3, 2, 'UserH');
INSERT INTO service_users  (service_id, user_id, username) VALUES (3, 3, 'UserI');
SELECT * FROM service_users  WHERE (service_id, user_id) IN (
    (1, 1),
    (2, 3) 
);
+------------+---------+----------+
| service_id | user_id | username |
+------------+---------+----------+
|          1 |       1 | UserA    |
|          2 |       3 | UserF    |
+------------+---------+----------+

　値のセットでWHERE INをしています。もしWHERE service_id IN (1,2) and user_id (1, 3)とすると結果はそれぞれのWHERE INの積集合となり、(1,1)や(2,3)といった余分な組み合わせもヒットしてしまいます。これをLaravelで書くと次のようになります。

// 複合主キーの値を用意
$request = [
    // service_id, user_id
    ['service_id' => 1, 'user_id' => 1],
    [2, 1], // 順番が大事。順番が合って入ればキー名は無くてもOK
    [2, 3],
];

// プレースホルダーの文字列を生成
// $requestと同じ長さの ['(?, ?)', '(?, ?)', '(?, ?)',...]という配列をつくり
// implodeでそれを繋いで (?, ?),(?, ?),(?, ?),... という文字列を生成
$placeholders = implode(',', array_fill(0, count($request), '(?, ?)'));

// プレースホルダーにあてはめるの値の配列をつくる
// ↑のプレースホルダーに当てはまるように一次元の配列を作ればいいので、LaravelのArr::flattenを使って二次元配列を一次元配列にする
$values = \Arr::flatten($request);

// クエリを実行
$serviceUsers = \App\Models\ServiceUser::query()
    ->whereRaw("(service_id, user_id) IN ($placeholders)", $values)
    ->get();
/*
    select
      *
    from
      `service_users`
    where
      (service_id, user_id) IN (
        (1, 1),
        (2, 1),
        (2, 3)
      )
 */
// いつものEloquentのコレクションが得られる
dd($serviceUsers);

　上記のコードをメソッドにまとめると次のようになります。

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Arr;

class ServiceUser extends Model
{
    /**
     * 複合主キーを使用してWHERE INクエリを実行する。
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param array $compositeKeys 複合キーの配列。例: [['service_id' => 1, 'user_id' => 1], [2, 1], ...]
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeWhereInForCompositePK(\Illuminate\Database\Eloquent\Builder $query, array $compositeKeys)
    {
        // プレースホルダーの文字列を生成
        $placeholders = implode(',', array_fill(0, count($compositeKeys), '(?, ?)'));

        // プレースホルダーに適用する値の配列を生成
        $values = Arr::flatten($compositeKeys);

        // whereRawを使ってクエリを実行
        return $query->whereRaw("(service_id, user_id) IN ($placeholders)", $values);
    }
}

// 使用例
$serviceUsers = ServiceUser::whereInForCompositePK([
    ['service_id' => 1, 'user_id' => 1],
    [2, 1],
    [2, 3],
])->get();

　結構力技ですが、こんな感じでできます。ちゃんとプレースホルダーを使わないと脆弱性につながるのでそこは注意する必要があります。

　余談ですがWHERE INにこだわらないなら次のようにORとANDの組み合わせで同じようにすることもできます。WHERE INと比べてどちらがどうなのかはEXPLAIN等をみて判断するのが良さそうです。

public function scopeWhereCompositePK(\Illuminate\Database\Eloquent\Builder $query, array $compositeKeys)
{
    $query->where(function ($query) use ($compositeKeys) {
        foreach ($compositeKeys as[$serviceId, $userId]) {
            $query->orWhere(function ($query) use ($serviceId, $userId) {
                $query->where('service_id', $serviceId)
                    ->where('user_id', $userId);
            });
        }
    });

    return $query;
}