CODEWITHSUNDEEP
phplaravellaravel-4
Martney Acha
Martney Acha

Reputation: 3002

Group Concat that use Eloquent/Raw Laravel Query

I have an Object structure that is store in Eloquent Form

{"item_id": "2",
"item_color": "Black",
"item_size": "L",
"item_Quantity": "5",},

{"item_id": "2",
"item_color": "Black",
"item_size": "M",
"item_Quantity": "5",},

{"item_id": "2",
"item_color": "Black",
"item_size": "S",
"item_Quantity": "5",},

{"item_id": "2",
"item_color": "White",
"item_size": "S",
"item_Quantity": "5",},

What I'm trying to achieve is to combine up all item_quantity which has the same item_id and item_color and Display in Table form like this.

ItemID ItemColor    L-M-S     Total
2         Black     5-5-5      15
2         White         5      5

This is my Current Query

$items = DB::table('item')
            ->select(DB::raw("item_id,item_color,GROUP_CONCAT(item_size SEPARATOR '-') as ItemSize,GROUP_CONCAT(item_Quantity SEPARATOR '-') as Quantity,sum(item_Quantity) as TOTAL"))
            ->groupBy('item_id','item_color')
            ->get();

This is my first Query that is already solved:

Display Table form with Eloquent Data Laravel

Upvotes: 2

Views: 5959

Answers (1)

Mustafa ASAN
Mustafa ASAN

Reputation: 3825

$items = DB::table('item')
            ->select(DB::raw("item_id,item_color,GROUP_CONCAT(item_Quantity SEPARATOR '-') as `L-M-S`,sum(item_Quantity) as TOTAL"))
            ->groupBy('item_id','item_color')
            ->get();

I hope this is what you need , point to remember when using hypen in column name you have to make it wrapped with backticks.

Upvotes: 4

Related Questions