I have the below query I am trying to write using query builder.
SELECT c.class_name
FROM jobs as j
INNER JOIN checks as c on
(c.clientD = j.clientID and c.subID = j.subID and c.start_visit_num <= j.visitnum)
or
(c.clientID = j.clientID and c.subID = 0 and c.start_visit_num <= j.visitnum)
WHERE j.jobid = ? and c.deleted = ?
ORDER BY c.subID desc, c.clientID desc, c.start_visit_num desc
LIMIT 1
I have converted it to the below, but the problem I have is the join is not correct, and does not group the two parts of the join.
DB::table('jobs as j')
->join('checks as c', function($join) {
$join->on('c.clientD', '=', 'j.clientD')
->on('c.subID ', '=', 'j.subID ')
->on('c.start_visit_num', '<=', 'j.visitnum');
$join->orOn('c.clientD', '=', 'j.clientD')
->on('c.subID ', '=', DB::raw('0'))
->on('c.start_visit_num', '<=', 'j.visitnum');
})
->where('j.jobid', $this->job->id)
->where('c.deleted', 0)
->orderBy('c.subID', 'DESC')
->orderBy('c.clientD', 'DESC')
->orderBy('c.start_visit_num', 'DESC')
->select('c.class_name')
->limit(1)
->toSql()
This builds the query as below
SELECT c.class_name
FROM jobs as j
INNER JOIN checks as c on c.clientD = j.clientID and c.subID = j.subID and c.start_visit_num <= j.visitnum or c.clientID = j.clientID and c.subID = 0 and c.start_visit_num <= j.visitnum
WHERE j.jobid = ? and c.deleted = ?
ORDER BY c.subID desc, c.clientID desc, c.start_visit_num desc
LIMIT 1
Is there an easy way to group the two parts of the join either side of the OR like the original query?