如何将给定多表统计原始SQL转换为Laravel Query Builder查询构造器

你可以通过Laravel的DB门面结合原生表达式方法实现转换，完整代码如下：

use Illuminate\Support\Facades\DB;

$result = DB::table('adopting_technologies')
    ->join('adopting_farmers', 'adopting_farmers.id', '=', 'adopting_technologies.adopting_farmer_id')
    ->leftJoin('cig_members', 'cig_members.id', '=', 'adopting_farmers.cig_member_id')
    ->leftJoin('farmers', 'farmers.id', '=', 'adopting_farmers.farmer_id')
    ->leftJoin('financial_years', 'financial_years.id', '=', 'adopting_farmers.financial_year_id')
    ->leftJoin('technologies', 'technologies.id', '=', 'adopting_technologies.technology_id')
    ->selectRaw("
        technologies.name_en,
        Count(cig_members.id) AS CigTotal,
        Count(CASE WHEN cig_members.is_ethnic = 1 THEN 1 ELSE NULL END) AS CigTotalEthnic,
        Count(CASE WHEN cig_members.gender = 'female' THEN 1 ELSE NULL END) AS CigTotalFemale,
        Count(CASE WHEN cig_members.gender = 'female' AND cig_members.is_ethnic = 1 THEN 1 ELSE NULL END) AS CigTotalEthnicFemale,
        Count(farmers.id) AS NonCigTotal,
        Count(CASE WHEN farmers.is_ethnic = 1 THEN 1 ELSE NULL END) AS NonCigtoTalEthnic,
        Count(CASE WHEN farmers.gender = 'female' THEN 1 ELSE NULL END) AS NonCigTotalFemale,
        Count(CASE WHEN farmers.gender = 'female' AND farmers.is_ethnic = 1 THEN 1 ELSE NULL END) AS NonCigtTotalEthnicFemale
    ")
    ->groupBy('adopting_technologies.technology_id')
    ->get();

补充注意事项 #

• 若你的项目开启了MySQL严格模式下的GROUP BY校验，需要把technologies.name_en也加入GROUP BY参数，或者根据业务场景调整数据库配置的严格模式规则。
• 统计逻辑直接放在selectRaw中即可，和原生SQL语法完全兼容，执行效率和直接运行原生SQL无差异。
• 后续需要新增查询条件、排序等逻辑时，直接在groupBy方法前链式调用对应的查询构造器方法即可。

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