convert mysql to query to laravel query

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();

What have you tried? Anyway - with complex queries like this, using the raw SQL is often more readable than using Laravel's query builder. https://laravel.com/docs/5.6/database#running-queries — Emile Pels, Jun 13 '18 at 11:28
what exactly you mean by not working? Any errors or output not as expected — NitinSingh, Jun 13 '18 at 11:46
i am not able to getting same result as mysql query in my laravel query. in mysql query it returns correct value for bundle items but in laravel query it returns wrong value for bundle items — Code Embassy, Jun 13 '18 at 11:53

score 1 · Accepted Answer · edited Jun 13 '18 at 12:43

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");

edited Jun 13 '18 at 12:43

Kirk Beard

9,569
12
43
47

answered Jun 13 '18 at 12:28

Leo Rams

669
9
24

ok i can use raw query but i have where condition is dynamic on this query. if status is set then my query will have status in where condition. – Code Embassy Jun 13 '18 at 12:38
You can include this as a where condition like the following: and IF('" . $condition . "' = some_value, put where clause condition here', 1) This will ensure that if the condition is met, then the condition is added to the query else return 1 (true) which won't affect your query. Let me know if this helps – Leo Rams Jun 13 '18 at 12:43

convert mysql to query to laravel query

1 Answers1