Laravel Join Example

PHP

static function future_summaries($member_id, $from=null, $to=null) { $result = DB::table('orders as a') ->join('futures as b', 'b.id', '=', 'a.future_id') ->select( 'a.future_id', 'b.name as future_name', DB::raw('sum(lot) as lot_total'), DB::raw('sum(quota) as quota_total'), DB::raw('sum(profit) as profit_total') ) ->where('a.member_id', $member_id) ->whereIn('a.status', [3, 4]) ->groupBy('a.future_id', 'b.name'); if(isset($from) == true){ $from = std_date($from); $result = $result->where('a.tx_time', '>', $from); } if(isset($to) == true){ $to = std_date(add_days($to, 1)); $result = $result->where('a.tx_time', '<', $to); } $result = $result->get(); return $result; }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

static function future_summaries($member_id, $from=null, $to=null)   {     $result = DB::table('orders as a')         ->join('futures as b', 'b.id', '=', 'a.future_id')         ->select(             'a.future_id',             'b.name as future_name',             DB::raw('sum(lot) as lot_total'),             DB::raw('sum(quota) as quota_total'),             DB::raw('sum(profit) as profit_total')         )         ->where('a.member_id', $member_id)         ->whereIn('a.status', [3, 4])         ->groupBy('a.future_id', 'b.name');       if(isset($from) == true){         $from = std_date($from);         $result = $result->where('a.tx_time', '>', $from);     }       if(isset($to) == true){         $to = std_date(add_days($to, 1));         $result = $result->where('a.tx_time', '<', $to);     }       $result = $result->get();       return $result;   }

Advanced:

PHP

static function exam_results($cert_id, $user_id){ $result = DB::table('Test_Groups_Test_Required as a') ->leftJoin('Test_Survey_Log as b', function($join) use ($user_id){ $join->on('b.survey_id', '=', 'a.test_id') ->where('b.result', '=', 1) ->where('b.user_id', '=', $user_id); }) ->select( 'a.*', 'b.result' ) ->where('a.group_id', $cert_id) ->get(); return $result; }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

static function exam_results($cert_id, $user_id){        $result = DB::table('Test_Groups_Test_Required as a')             ->leftJoin('Test_Survey_Log as b', function($join) use ($user_id){                 $join->on('b.survey_id', '=', 'a.test_id')                     ->where('b.result', '=', 1)                     ->where('b.user_id', '=', $user_id);             })             ->select(                 'a.*',                 'b.result'             )             ->where('a.group_id', $cert_id)             ->get();                  return $result;     }