时间戳

首页 > 解决方案 > 将原始 SQL 转换为 Laravel 查询构建器

php - 将原始 SQL 转换为 Laravel 查询构建器

问题描述

我很难将此 SQL 查询转换为 Laravel 查询生成器。我尝试了几个小时，但无法解决这个问题。我尝试了将 SQL 转换为查询生成器的在线工具，但没有奏效。

这是我的代码：

SELECT 
   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
   FROM   adopting_technologies
   JOIN adopting_farmers
          ON adopting_farmers.id = adopting_technologies.adopting_farmer_id
   LEFT JOIN cig_members
          ON cig_members.id = adopting_farmers.cig_member_id
   LEFT JOIN farmers
          ON farmers.id = adopting_farmers.farmer_id
   LEFT JOIN financial_years
          ON financial_years.id = adopting_farmers.financial_year_id
   LEFT JOIN technologies
          ON technologies.id = adopting_technologies.technology_id
   GROUP  BY adopting_technologies.technology_id

我怎样才能转换这个？

标签： phpsqllaravellaravel-query-builder

解决方案

它应该大致如下所示：

$result = AdoptingTechnology::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
         ")
            ->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')
            ->groupBy('adopting_technologies.technology_id')
            ->get();

如果您有任何问题，请告诉我:) ofc 无法在本地测试

推荐阅读