Laravel group by 带有内联选择语句的问题

Question

下面的 SQL 工作正常。它正在考虑并在问题所在的 Laravel 中实施它。该sqlfiddle可以发现在这里。

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' 列。

我的 Laravel 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 选择，它就会出现。

Answer 1

这是由Tray2在Laracasts 上回答的：确认以下解决方案有效：$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');

Answer 2

Laravel 很可能将那些用反引号包裹起来，这会导致 MySQL 相信它们是某个表中的实际列。也尝试将它们包裹起来DB::raw。$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() ;