再會。請問如何將原始 sql 查詢轉換為 Laravel 查詢構建器。用raw sql，無法分頁，資料相當多。
$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);
我修改了@MohammedHassan 的回復。謝謝
uj5u.com熱心網友回復：
通常，SQL 陳述句在Laravel 查詢構建器中具有等效性，當您需要使用函式時，您將陳述句包裝在DB::raw().
您的查詢可以像這樣重寫：
$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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/354205.html
上一篇：PowerQuery條件聯接