[PHP][Laravel][SQL] LaravelのEloquentで WHERE A OR B を実施（条件によりorWhereæ¡ä»¶ã‚’è¿½åŠ ï¼‰

バージョン

Laravel 6.0

概要

Eloquentで以下を表現する。

WHERE (hoge = 1 AND fuga = 2) OR (foo = 3 AND bar = 4)

さらにOR条件は条件分岐により追加する/しないを判断させる。

実現したいSQL

SELECT
  students.name, clubs.name
FROM
  students
LEFT JOIN
  clubs ON students.club_id = clubs.id AND clubs.code = 4
WHERE
  (students.grade > 5 and clubs.type = 1)
  OR
  (students.grade in (1,3) and students.area_code = 10)
ORDER BY
  students.created_at desc

Models/student.php

$query = self::select(['students.name as students_name', 'clubs.name as clubs_name'])
        ->leftjoin('clubs', function ($join) {
            $join->on('students.club_id', '=', 'clubs.id')
                >where('clubs.code', config('club.code.D'));
            })
        ->where(function ($q) use ($grade) {
            $q->orWhere('students.grade', '>', $grade)
                >where('clubs.type', config('club.type.BUNKAKEI'));
            });

if ($grades) {
    $query = $query->orWhere(function ($q) use ($grades) {
        $q->orWhereIn('students.grade', $grades)
            ->where('students.area_code', config('students.area_code.TOKYO'));
    });

return $query->orderBy('students.created_at', 'desc')
    ->get()
    ->toArray();
}

PHPフレームワーク Laravel実践開発