Laravel: Use complex condition in JOIN with Fluent

laravel laravel-3
jmadsen picture jmadsen · Apr 18, 2013 · Viewed 7.8k times · Source

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'));
 })

Answer

Phill Sparks picture Phill Sparks · Apr 18, 2013

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

->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', '=', DB::raw(1))
    $join->or_on( 'camp_to_cabin.camp_id', '=', DB::raw(null))
})

Related questions

Convert laravel object to array

Laravel output: Array ( [0] = stdClass Object ( [ID] = 5 ) [1] = stdClass Object ( [ID] = 4 ) ) I want to convert this into normal array. Just want to remove that stdClass Object. I also tried using ->toArray(); but I get an error: Call to a member …

Read More
Laravel - Pass more than one variable to view

I have this site and one of its pages creates a simple list of people from the database. I need to add one specific person to a variable I can access. How do I modify the return $view->with(…

Read More
My Routes are Returning a 404, How can I Fix Them?

I've just started learning the Laravel framework and I'm having an issue with routing. The only route that's working is the default home route that's attached to Laravel out of the box. I'm using WAMP on Windows and it uses …

Read More