|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
if(val($options, 'extra_options') != false){ $ex_list = val($options, 'extra_options'); $result = $result->where(function($query) use ($ex_list){ if(in_array(1, $ex_list) == true){ $query->orWhere('remain_quota', '>', 0); } if(in_array(2, $ex_list) == true){ $query->orWhere('remain_quota', '<=', 0); } if(in_array(3, $ex_list) == true){ $query->orWhere('status', 3); } if(in_array(4, $ex_list) == true){ $query->orWhere('status', 2); } }); } |
Raw SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$cert_id = val($options, 'cert_id'); if($cert_id != false){ $result = $result->whereRaw("a.id in (select distinct test_id from Test_Groups_Test_Required where group_id = " . $cert_id . ")"); } $keyword = val($options, 'keyword'); if($keyword != false){ $result = $result->where(function($query) use ($keyword){ //$find = "'%" . $keyword . "%'";//<-This will always return empty $find = '%' . $keyword . '%'; $query->orWhere('b.name', 'like', $find); $query->orWhere('b.pid', 'like', $find); $query->orWhere('c.name', 'like', $find); $query->orWhere('c.pid', 'like', $find); $query->orWhereRaw(DB::raw("a.id in (select order_id from order_cvs_code where code like " . $find . ")")); }); } |
Compare Date:
|
1 2 3 4 5 6 7 8 9 10 11 |
$active = val($options, 'active'); //logg($active); if($active !== false){ if($active == 1){ $d = Carbon::now()->toDateTimeString(); $result = $result->whereDate('offline_time', '>=', $d); }else{ $d = Carbon::now()->toDateTimeString(); $result = $result->whereDate("offline_time", '<', $d); } } |
Get not available coupons
|
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 |
public function coupons($active=true){ DB::enableQueryLog(); $now = Carbon::now()->toDateString(); if($active == true){ $data = DB::table('member_coupons as a') ->join('coupon as b', 'b.id', '=', 'a.coupon_id') ->where('a.member_id', $this->id) ->where('a.consumed', 'n') ->where('b.valid_from', '<', $now) ->where('b.valid_to', '>=', $now) ->get(); }else{ $data = DB::table('member_coupons as a') ->join('coupon as b', 'b.id', '=', 'a.coupon_id') ->where('a.member_id', $this->id) ->whereRaw(DB::raw("(a.consumed = 'y' or b.valid_from > '" . $now . "' or b.valid_to < '" . $now . "')")) ->get(); } $queries = DB::getQueryLog(); logg(end($queries)); return $data; } |
Result
|
1 2 3 4 5 6 |
select * from `member_coupons` as `a` inner join `coupon` as `b` on `b`.`id` = `a`.`coupon_id` where `a`.`member_id` = 1 and (a.consumed = 'y' or b.valid_from > '2020-07-25' or b.valid_to < '2020-07-25') |