php - Laravel group by 问题与内联选择语句

Question

下面的 SQL 工作正常。它正在采取这一点并在问题所在的 Laravel 中实现它。可以找到 sqlfiddle here .

SELECT provider_id
     , o.shortName
     , count(b.site_id) as sites
     , ( select b.depositFee 
              + ( select sum(depositFee) 
                    from backhaul 
                   where backhaul.parent_id=b.id
                ) 
       ) as entranceFee
     , ( select b.rightOfWayCharges 
              + ( select sum(rightOfWayCharges) 
                    from backhaul 
                   where backhaul.parent_id = b.id
                )
       ) as rowFee
    FROM backhaul AS b
    JOIN organisation AS o  
      ON (b.provider_id = o.id)
   WHERE isnull(b.parent_id)
   GROUP 
      BY provider_id
       , entranceFee
       , rowFee

该表是递归的。这给了我以下这些结果:

--------------------------------------------------------
provider_id | sshortName | sites | entranceFee | rowFee
--------------------------------------------------------
802 | TM | 1 | 12500.00 | 7500.00  
803 | TIME | 1 | 7500.00 | 0.00

问题是 Laravel 似乎无法识别 group by 语句中的“entranceFee”或“rowFee”列。

我的 Lararvel 5.7 实现如下所示:

$sql = DB::raw("provider_id, o.shortName, count(b.site_id) as sites," .
"(select b.depositFee + (select sum(depositFee) from backhaul where backhaul.parent_id=b.id)) as entranceFee," .
"(select b.rightOfWayCharges + (select sum(rightOfWayCharges) from backhaul where backhaul.parent_id=b.id)) as rowFee") ;

$data = DB::table("backhaul as b")->select($sql)
   ->join("organisation as o", "b.provider_id", "=", "o.id")
   ->whereNull("b.parent_id")
   ->groupBy("b.provider_id", DB::raw("entranceFee"), DB::raw("rowFee"))
   ->paginate() ;

关于 GROUP BY 语句中缺少 SELECT #4 的错误，我得到了通常的 MySQL 组。

我已经尝试添加为数组 groupBy(["provider_id", DB::raw("entranceFee"), DB::raw("rowFee")]) 但它仍然是 barfs .如果我也添加内联 SQL 选择，它会 barfs。

Answer 1

这是在 Laracasts 上回答的通过托盘 2:

以下解决方案已确认有效:

$data = DB::select('SELECT provider_id
     , o.shortName
     , count(b.site_id) as sites
     , ( select b.depositFee 
              + ( select sum(depositFee) 
                    from backhaul 
                   where backhaul.parent_id=b.id
                ) 
       ) as entranceFee
     , ( select b.rightOfWayCharges 
              + ( select sum(rightOfWayCharges) 
                    from backhaul 
                   where backhaul.parent_id = b.id
                )
       ) as rowFee
    FROM backhaul AS b
    JOIN organisation AS o  
      ON (b.provider_id = o.id)
   WHERE isnull(b.parent_id)
   GROUP 
      BY provider_id
       , entranceFee
       , rowFee');