Geek Answers Handbook

Laravel: Use complex condition in JOIN with Fluent

Due to the complex schema and library, which requires either Fluent or Eloquent (and 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 a compound condition; I tried callbacks and everything else that I can think of, but cannot get the correct syntax to generate.

I 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 backlinks around my 1 and null (I know the zero bit is wrong - experimenting), which I cannot get rid of; otherwise it looks pretty close

Any help?

TIA

Thanks Phil - final answer:

 ->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')); })
+6
source share
1 answer

It looks like you need to use DB::raw() , otherwise ->on() expects two columns. Sort of...

 ->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)) })
+12
source

More articles:


All Articles