Advanced where clause – annonymous function & like

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:

$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:

$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

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

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 &gt; '2020-07-25' 
or b.valid_to &lt; '2020-07-25')