@Sinnbeck Sir if possible will you be able to sugeest as the answer provided by Lary A.I did not meet the requirements as it is trying to search the score as a separate column whereas score is a created column
Feb 27, 2023
1
Level 1
Trying to change the a sql query in laravel 9
I am trying to convert the following sql query:
SELECT state , county , COUNT(1), (COUNT(1)*200) AS score FROM lawyer_subscribers LEFT JOIN lawyer_subscriber_case_types ON lawyer_subscribers.id = lawyer_subscriber_case_types.subscriber_id WHERE casetype = 'Family Law' GROUP BY state , county ORDER BY state , county;
I have tried it in laravel 9 as following:
public function subscriber_group_data(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 {
$score = explode(" ",$request->score);
$status = ($request->status == 'Active') ? 1 : 0;
$subscriberrecords = LawyerSubscriber::leftJoin('lawyer_subscriber_case_types', 'lawyer_subscribers.id', '=', 'lawyer_subscriber_case_types.subscriber_id')
->select('state','county')
->selectRaw('COUNT(1)')
->selectRaw('(COUNT(1)*200) AS score')
->whereIn('casetype',$request->case_type)
->where('status',$status)
->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();
}
}
The query is returning the result.
Now I want the score calculated as (COUNT(1)*200) must be within the range of the score parameter in the json.
The json being sent is :
{
"case_type" : ["Family Law"],
"status" : "Active",
"score" : "200 To 999"
}
Now if I want the response to be returned if the score calculated lies between the range provided such as 200 and 999. Kindly suggest what changes to be done in the query?
Please or to participate in this conversation.