在Laravel中使用原始sql查询

Question

大家好。我有一个sql查询，我已经测试和使用mysql工作台工作。但是，我想将它集成到我的Laravel应用程序中，这样我就可以对结果使用get()和paginate()函数。查询如下

所示

SELECT applicable_areas.area
, applicable_areas.id as id
, count(stype) as supreme_court_cases
, count(atype) as appeal_court_cases
FROM applicable_areas
JOIN (
SELECT    1 AS atype, null AS stype, applicable_area_id, suitno FROM appeal_applicable_areas
UNION
SELECT null         ,    1         , applicable_area_id, suitno FROM supreme_applicable_areas
) AS area_types
ON area_types.applicable_area_id = applicable_areas.id
JOIN cp_cases_counsel
ON cp_cases_counsel.suitno = area_types.suitno
WHERE cp_cases_counsel.counsel_id = 38
GROUP BY applicable_areas.id
ORDER BY applicable_areas.area ASC;

在Laravel

$practice_areas = DB::connection('mysql2')->table('applicable_areas')
->select(DB::raw("applicable_areas.area
, applicable_areas.id as id
, COUNT(stype) as supreme_court_cases
, COUNT(atype) as appeal_court_cases
FROM applicable_areas
JOIN (
SELECT    1 AS atype, null AS stype, applicable_area_id, suitno FROM appeal_applicable_areas
UNION
SELECT null         ,    1         , applicable_area_id, suitno FROM supreme_applicable_areas
) AS area_types
ON area_types.applicable_area_id = applicable_areas.id
JOIN cp_cases_counsel
ON cp_cases_counsel.suitno = area_types.suitno
WHERE cp_cases_counsel.counsel_id = 38
GROUP BY applicable_areas.id
ORDER BY applicable_areas.area ASC"))->get();

也试过了:

$practice_areas = DB::connection('mysql2')->table('applicable_area')
->join(DB::raw("SELECT 1 AS atype, null AS stype, applicable_area_id, suitno FROM appeal_applicable_areas
UNION
SELECT null , 1 , applicable_area_id, suitno FROM supreme_applicable_areas
) AS area_types"),
function($join){
$join->on('area_types.applicable_area_id', '=', 'applicable_areas.id');
})
->join('cp_cases_counsel', 'cp_cases_counsel.suitno','=','area_types.suitno')
->select('applicable_areas.id as id','applicable_areas.area','count(stype) as supreme_court_cases',
'count(atype) as appeal_court_cases')
->where('cp_cases_counsel.counsel_id',38)
->groupBy('applicable_areas.id')->get();```
I get an error with that statement. Please, is there a way to do this?
Thanks.

Answer 1

$practice_areas = DB::select(" select applicable_areas.area ., applicable_areas。Id为Id， COUNT(type)为supreme_court_cases， COUNT(type)作为appeal_court_cases从applicable_areas加入(SELECT 1 AS type, null AS type, applicable_area_id, suitno FROM appeal_applicable_areas联盟SELECT null, 1, applicable_area_id, suitno FROM supreme_applicable_areas) AS area_typesarea_types。Applicable_area_id = applicable_area .id加入cp_cases_counselcp_cases_counsel。Suitno = area_types.suitnocp_cases_counsel的地方。advis_id = 38按applicable_areas.id分组按applicable_areas排序。面积ASC")→get (),