[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(); }