[FIXED] How to convert raw Database query to Query Builder in Laravel

Issue

Good day. Please how can I convert a raw sql query to Laravel query builder. With the raw sql, I am unable to paginate, and the data are quite numerous.

$cpCounsel = DB::table('cp_counsel as A')
    ->select([
        'A.enrolment_number as id',
        DB::raw('MIN(A.counsel) as counsel'),
    ])
    ->groupBy('enrolment_number');

$counsels = DB::table('cp_cases_counsel as T')
    ->joinSub($cpCounsel, 'A.id', '=', 'T.counsel_id')
    ->where('A.counsel', 'like', "%$request->search_term%")
    ->select([
        'T.counsel_id',
        'A.counsel',
        DB::raw('COUNT(T.counsel_id) as total'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0)) as supreme_court_cases'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as supreme_court_cases_as_lead'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 2, 1, 0)) as supreme_court_cases_as_supporting'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0)) as appeal_court_cases'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as appeal_court_cases_as_lead'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0) AND if(T.counsel_role = 2, 1, 0)) as appeal_court_cases_as_supporting'),
    ])
    ->groupBy('T.counsel_id', 'A.counsel')
    ->paginate(15);

I have modified @MohammedHassan's reply. Thanks

Solution

Usually, SQL statements have equivalent in Laravel query builder and when you need to use a function you wrap your statement in DB::raw().

Your query can be rewritten like this:

$cpCounsel = DB::table('cp_counsel as A')
    ->select([
        'A.enrolment_number as id',
        DB::raw('MIN(A.counsel) as counsel'),
    ])
    ->groupBy('enrolment_number');

$counsels = DB::table('cp_cases_counsel as T')
    ->joinSub($cpCounsel, 'A.id', '=', 'T.counsel_id')
    ->where('A.counsel', 'like', "%$request->search_term%")
    ->select([
        'T.counsel_id',
        'A.counsel',
        DB::raw('COUNT(T.counsel_id) as total'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0)) as supreme_court_cases'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as supreme_court_cases_as_lead'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 2, 1, 0)) as supreme_court_cases_as_supporting'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0)) as appeal_court_cases'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as appeal_court_cases_as_lead'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0) AND if(T.counsel_role = 2, 1, 0)) as appeal_court_cases_as_supporting'),
    ])
    ->groupBy('T.counsel_id', 'A.counsel')
    ->paginate(15);

Answered By - muhamadhhassan