Laravel: Use complex condition in JOIN with Fluent

Question

For reasons of a complex schema & a library that requires either Fluent or Eloquent to be used (not just raw DB::query() ), I need to create:

LEFT JOIN `camp_to_cabin` 
ON `camper_to_cabin`.`cabin_id` = `camp_to_cabin`.`cabin_id`
AND `camp_to_cabin`.`camp_id` =1 OR `camp_to_cabin`.`camp_id` IS NULL

as the join clause; I've tried the callbacks & everything else I can think of, but cannot get the proper syntax to generate.

I have tried:

    ->left_join('camp_to_cabin', function ($join){
        $join->on( 'camper_to_cabin.cabin_id', '=', 'camp_to_cabin.cabin_id')
        $join->on( 'camp_to_cabin.camp_id', '=', 1)
        $join->on( 'camp_to_cabin.camp_id', '=', null)

    })

but it puts backticks around my 1 & null (I know the null bit isn't right - experimenting) that I can't get rid of; otherwise it looks pretty close

Any help?

TIA

---

Thanks, Phil - final answer is:

->left_join('camp_to_cabin', function ($join) use ($id){
$join->on( 'camper_to_cabin.cabin_id', '=', 'camp_to_cabin.cabin_id');
$join->on( 'camper_to_cabin.cabin_id', '=', DB::raw($id));
$join->or_on( 'camper_to_cabin.cabin_id', 'IS', DB::raw('NULL'));
 })

Fulvous

Answer 1

Looks like you need to use DB::raw() otherwise ->on() expects two columns. Something like...

->left_join('camp_to_cabin', function (\Illuminate\Database\Query\JoinClause $join) use ($id){
    $join->on( 'camper_to_cabin.cabin_id', '=', 'camp_to_cabin.cabin_id')
    $join->on( 'camp_to_cabin.camp_id', '=', DB::raw($id))
    $join->or_on( 'camp_to_cabin.camp_id', '=', DB::raw('NULL'))
})

Lyra

lovely! I'll check that later and come back to let you know - thanks so much. – Fulvous

that was pretty close - the final solution I posted in my question above. thanks again! – Fulvous

Add type hint in $join will be helpful. – Misspell