親子関係のテーブルでのクエリーの作成（クエリビルダー編）

いつもの例を使いますと、商品productと商品画像product_imageの親子関係、つまり、１対多の関係があるとして、これに対して検索画面を作成するとします。

検索画面では、商品名だけでなく、商品画像のMIMEも検索項目として、検索できるようにします。つまり、親のテーブルの項目（商品名）でなく、子のテーブルの項目（MIME)も指定可能とします。

この場合、すぐに思いつくのは、以下のようにjoinして、その検索結果を表示です。

//検索値
$input[] = [
    'name' => '商品名',
    'mime' => 'image/gif'
];

$products = DB::table('product')
    ->join('product_image', 'product.product_id', '=', 'product_image.product_id')
    ->where('product.name', $input['name'])
    ->where('product_image.mime', $input['mime'])
    ->get();

しかし、これでは検索結果の各行は、商品画像のレコードとなってしまいます。１商品に対して、複数のGIF画像があるときは、複数分商品が表示されます。今回は、該当する商品のみを表示したいです。

となると、

$products = DB::table('product')
    ->join('product_image', 'product.product_id', '=', 'product_image.product_id')
    ->where('product.name', $input['name'])
    ->where('product_image.mime', $input['mime'])
    ->groupBy('product.product_id')
    ->get();

あるいは、joinでなくwhereInを使用して、

$products = DB::table('product')
    ->where('product.name', $input['name'])
    ->whereIn('product.product_id', function($query) use($input) {
        $query->from('product_image')->select('product_id')->where('mime', $input['mime']);
    })
    ->get();

$query->tableでなく、$query->fromというところがちょいとややこしいですね。しかし、親のレコードだけを引き出すという点では、joinを使用するよりわかりやすいです。

実効すると、このSQL文は以下のようになります。

select * from `product` where `name` = '商品名' and `product`.`product_id` in (select `product_id` from `product_image` where `mime` = 'image/gif')