Feb 28, 2023
0
Level 1
Trying to change the following sql query in laravel
I am trying to convert the following sql query :
SELECT state,
county,
COUNT(1),
(COUNT(1)*200) AS score
FROM lawyer_subscribers
WHERE id IN (SELECT subscriber_id
FROM lawyer_subscriber_case_types
WHERE casetype IN ('Family Law'))
AND status = 1
GROUP BY state,
county
HAVING (COUNT(1)*200) >= 200 AND (COUNT(1)*200) <= 999
ORDER BY state,
county;
I have tried it in the following way:
public function subscriberGroupData(Request $request) {
$returnArray = array();
$validator = Validator::make($request->all(), [
'case_type' => 'required|array',
'status' => 'required',
'score' => 'required'
]);
if ($validator->fails()) {
return response()->json(['error' => $validator->errors()], 401);
}
try {
$subscriberrecords = array();
$trimscore = '';
$score = '';
if($request->score == '5000+') {
$trimscore = rtrim($request->score , '+');
}else{
$score = explode("-",$request->score);
}
$status = ($request->status == 'active') ? 1 : 0;
$records = array();
$case_type = $request->case_type;
$status = $request->status;
$records = LawyerSubscriber::select('state','county')
->selectRaw('COUNT(1)')
->selectRaw('(COUNT(1)*200) AS score')
->when(($status!='All'),function($query) use($status) {
$query->whereIn('status',$status);
})
->whereIn('id', function($query){
$query->select('subscriber_id')
->from(with(new LawyerSubscriberCaseType)->getTable())
->when(!in_array('All',$case_type),function($query) use($case_type){
$query->whereIn('casetype',$case_type);
})
})
->when(empty($trimscore),function($query) use($score){
$query->whereRaw('(COUNT(1)*200) >= ? AND (COUNT(1)*200) <= ?',$score[0],$score[1]);
},function($query) use($trimscore){
$query->whereRaw('(COUNT(1)*200) >= ?',$trimscore);
})
->groupBy('state', 'county')
->orderBy('state')
->orderBy('county')
->get();
$returnArray['status'] = "Success";
$returnArray['data'] = $subscriberrecords;
return response()->json($returnArray, 200);
}
catch( Exception $e ) {
echo 'Exception Message: ' .$e->getMessage();
}
}
It is showing an error in the when condition inside the whereIn loop. Can when condition be put inside a whereIn condition?
Please or to participate in this conversation.