Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

ankur_g's avatar

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?

0 likes
0 replies

Please or to participate in this conversation.