Laravel group by issues with an inline select statement

I've got some very straight forward SQL that generates the results I want:

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

The table is recursive. Which gives me these results in MySQL Workbench:

---

provider_id | sshortName | sites | entranceFee | rowFee

802 | TM | 1 | 12500.00 | 7500.00
803 | TIME | 1 | 7500.00 | 0.00

My Lararvel 5.7 implementation looks like this:

$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")
   ->paginate() ;

I'm getting the usual MySQL group by error about SELECT #4 missing from the GROUP BY statement.

I've tried adding groupBy("provider_id", "entranceFee", "rowFee") but still it barfs. If I add the SQL as well, it barfs.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2YVriPm
via IFTTT