dongsaolian8786
2018-06-13 11:26
浏览 82
已采纳

将mysql转换为查询到laravel查询

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();
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongu189744321158 2018-06-13 12:28
    已采纳

    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");
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题