如何在Laravel中将原始数据库查询转换为查询生成器

Question

大家好。请如何将原始sql查询转换为Laravel查询生成器。使用原始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的回复。由于

Answer 1

通常，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);

Answer 2

最后使用laravel DB joinSub方法得到了这个