John Davidson

Message:

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

---

My Answer:
Here is the modified version of your raw SQL query converted to Laravel Query Builder:

php
$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', function ($join) {
$join->on('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);


This code snippet converts your raw SQL query to Laravel Query Builder syntax. It also includes pagination for the results. Let me know if you need any further assistance.