Laravel中使用原生SQL查询并支持get()、paginate()方法的问题

• 第一种写法错误：将完整SQL语句传入select()方法，select()仅用于定义返回字段，不能承载完整查询逻辑，直接导致语法错误。
• 第二种写法错误点：
  1. 主表名拼写错误：table('applicable_area')应为applicable_areas，缺失末尾的s
  2. 聚合函数count()未用DB::raw()包裹，Laravel会将其识别为普通字段名，生成错误的SQL语法
  3. 若MySQL开启了only_full_group_by模式，select中的applicable_areas.area未加入groupBy，也会触发报错

推荐先单独构建UNION子查询，代码可读性更高，也方便后续调整参数，同时完全兼容get()、paginate()等Laravel查询构造器方法：

// 先构建UNION的子查询
$unionQuery = DB::connection('mysql2')->table('appeal_applicable_areas')
    ->select(DB::raw('1 AS atype, null AS stype, applicable_area_id, suitno'))
    ->union(
        DB::connection('mysql2')->table('supreme_applicable_areas')
            ->select(DB::raw('null AS atype, 1 AS stype, applicable_area_id, suitno'))
    );

// 主查询
$practice_areas = DB::connection('mysql2')->table('applicable_areas')
    ->joinSub($unionQuery, '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',
        'applicable_areas.area',
        DB::raw('COUNT(stype) as supreme_court_cases'),
        DB::raw('COUNT(atype) as appeal_court_cases')
    )
    ->where('cp_cases_counsel.counsel_id', 38)
    ->groupBy('applicable_areas.id', 'applicable_areas.area') // 兼容only_full_group_by模式
    ->orderBy('applicable_areas.area')
    ->get();
// 如需分页直接把->get()换成->paginate(每页数量)即可，比如->paginate(10)

如果偏好和原生SQL结构更接近的写法，也可以用以下方式：

$practice_areas = DB::connection('mysql2')->table('applicable_areas')
    ->join(DB::raw('(
        SELECT 1 AS atype, null AS stype, applicable_area_id, suitno FROM appeal_applicable_areas
        UNION
        SELECT null AS atype, 1 AS stype, applicable_area_id, suitno FROM supreme_applicable_areas
    ) area_types'), 'area_types.applicable_area_id', '=', 'applicable_areas.id')
    ->join('cp_cases_counsel', 'cp_cases_counsel.suitno', '=', 'area_types.suitno')
    ->select(
        'applicable_areas.id',
        'applicable_areas.area',
        DB::raw('COUNT(stype) as supreme_court_cases'),
        DB::raw('COUNT(atype) as appeal_court_cases')
    )
    ->where('cp_cases_counsel.counsel_id', 38)
    ->groupBy('applicable_areas.id', 'applicable_areas.area')
    ->orderBy('applicable_areas.area')
    ->get();

内容的提问来源于stack exchange，提问作者Korlahwarleh