Laravel Join AND Condition

$categoryRec = DB::table('categories')

->leftJoin('carts',function($join){

$join->on('categories.id','=','carts.course_id')

->where('carts.user_id', '=', auth()->user()->id);

})

->where(['categories.delete_flag'=>1])->orderBy('categories.id','DESC')

->select('categories.*',DB::raw("CASE WHEN carts.user_id = ". auth()->user()->id." THEN 1 ELSE 0 END as 'buy_status'"))

->get();

OR

$categoryRec = DB::table('categories')

->leftJoin('carts',function($join){

$join->on('categories.id','=','carts.course_id')

$join->on('carts.publish_date','>=',DB::raw("'2012-05-01'"));

})

->where(['categories.delete_flag'=>1])->orderBy('categories.id','DESC')

->select('categories.*',DB::raw("CASE WHEN carts.user_id = ". auth()->user()->id." THEN 1 ELSE 0 END as 'buy_status'"))

->get();

OUTPUT:

SELECT

`categories`.*,

CASE

WHEN carts.user_id = 2 THEN

1 ELSE 0 

END AS 'buy_status' 

FROM

`categories`

LEFT JOIN `carts` ON `categories`.`id` = `carts`.`course_id` 

AND `carts`.`user_id` = 2 

WHERE

( `categories`.`delete_flag` = 1 ) 

ORDER BY

`categories`.`id` DESC

Share on Google Plus

About Ram Pukar

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.