Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

coxy121's avatar
Level 20

How to write this join using query builder

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?

0 likes
3 replies
bobbybouwmann's avatar
Level 88

I think you need to use another layer of closures here. The on method accepts a closure as well. So something like this (( mistakes included ;))

DB::table('jobs as j')
    ->join('checks as c', function($join) {
        $join->on(function ($join) {
            $join->on('c.clientD', '=', 'j.clientD')
                ->on('c.subID ', '=', 'j.subID ')
                ->on('c.start_visit_num', '<=',  'j.visitnum');
        });

        $join->orOn(function ($join) {
            $join->on('c.clientD', '=', 'j.clientD')
                ->on('c.subID ', '=',  DB::raw('0'))
                ->on('c.start_visit_num', '<=',  'j.visitnum');
        });
})->toSql();
2 likes
bobbybouwmann's avatar

Another option would be using a raw query

DB::select(`c.class_name`)
        ->from(`jobs as j`)
        ->join(`checks as c`, function($join) {
            $join->on(DB::raw(`( c.clientD = j.clientID and c.subID = j.subID and c.start_visit_num <= j.visitnum )` ))
                ->on(DB::raw(`( c.clientID = j.clientID and c.subID = 0 and c.start_visit_num <= j.visitnum )` ), `or`);
            })
        ->where(`j.jobid`, `=`, `?`)
        ->where(`c.deleted`, `=`, `?`)
        ->orderBy(`c.subID`, `DESC`)
        ->orderBy(`c.clientID`, `DESC`)
        ->orderBy(`c.start_visit_num`, `DESC`)
        ->limit(1)
        ->get();

Please or to participate in this conversation.