Join Multiple Columns on Laravel Query Builder-CodePudding

I have no idea how to write it in Laravel Query Builder. Here's the SQL query:

select l.id, l.title, is1.similarity
FROM l
left join is1 on (l.id = is1.listing_id1 and is1.listing_id2 = 225678) or (l.id = is1.listing_id2 and is1.listing_id1 = 225678)

CodePudding user response：

I guess, it would be something like this:

DB::table('l')
->('l.id', 'l.title', 'is1.similarity')
 ->join('is1', function ($join) {
    $join->on('l.id', '=', 'is1.listing_id1') ->where('is1.listing_id2', '=', '225678')
         ->orOn('l.id','=',  'is1.listing_id2')->where('is1.listing_id1', '=', '225678');
 })->get();

Don't forget to import this:

use Illuminate\Support\Facades\DB;

More information can be find here.

CodePudding user response：

If I simply translate your SQL query to Laravel Query Builder query It would look like this.

DB::table('l')->select('l.id', 'l.title', 'is1.similarity')
 ->leftJoin('is1', function ($leftJoin) { 
    $leftJoin->on('l.id', '=', 'is1.listing_id1')->where('is1.listing_id2', '=', '225678')
     ->orOn('l.id','=',  'is1.listing_id2')->where('is1.listing_id1','=', '225678'); })
->get();