将mysql转换为查询到laravel查询

Question

Hello i am trying to convert this mysql query to laravel query but still i have no success. can you please help to convert this mysql query to laravel query.

Mysql query as below

select *,SUM(pp.starting_balance) as total from (select `aa`.*, 
GROUP_CONCAT( CONCAT(cc.purchasedescription," (",bb.quantity,")") SEPARATOR " , ") as bundle_item 
from `composite_inventories` as aa 
left join 
`composite_has_inventories` as bb on `aa`.`id` = `bb`.`composite_inventory_id` 
left join 
`inventories` as cc on `bb`.`inventory_id` = `cc`.`id` 
where 
`aa`.`subscriber_id` = '2'
group by 
`aa`.`id`) as tt left join `composite_has_warehouses` as pp on `tt`.`id` = `pp`.`composite_inventory_id` group by pp.composite_inventory_id

I tried to build as below but not working

$row = DB::table('composite_inventories')->select('composite_inventories.*',
            DB::raw('SUM(composite_has_warehouses.starting_balance) as total')
            DB::raw('GROUP_CONCAT( CONCAT(inventories.purchasedescription," (",composite_has_inventories.quantity,")") SEPARATOR " , ") as bundle_item')
            )
            ->leftJoin('composite_has_inventories', 'composite_inventories.id', '=', 'composite_has_inventories.composite_inventory_id')
            ->leftJoin('inventories', function($join) {
                $join->on('composite_has_inventories.inventory_id', '=', 'inventories.id');
            })

            ->where('composite_inventories.subscriber_id',$subscriber_id)
            ->groupBy('composite_inventories.id')
            ->leftJoin('composite_has_warehouses', 'composite_inventories.id', '=', 'composite_has_warehouses.composite_inventory_id')
            ->get();

Answer 1

You can use a DB::select() method and place your raw query as a string:

DB::select("select *,SUM(pp.starting_balance) as total from (select `aa`.*,
DB::raw('GROUP_CONCAT( CONCAT(cc.purchasedescription,' (',bb.quantity,')') SEPARATOR ' , ') as bundle_item ')
from `composite_inventories` as aa 
left join 
`composite_has_inventories` as bb on `aa`.`id` = `bb`.`composite_inventory_id` 
left join 
`inventories` as cc on `bb`.`inventory_id` = `cc`.`id` 
where 
`aa`.`subscriber_id` = '2'
group by 
`aa`.`id`) as tt left join `composite_has_warehouses` as pp on `tt`.`id` = `pp`.`composite_inventory_id` group by pp.composite_inventory_id");