Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have this query
SELECT item.name as Produk, outgoing.date as Tanggal, sum(outgoing_item.quantity) as Jumlah
FROM outgoing, outgoing_item, outgoing_type, item
WHERE item.id = outgoing_item.item_id
AND outgoing_item.outgoing_id = outgoing.id
AND outgoing_type.id = outgoing.outgoing_type_id
AND outgoing_type_id = 1
GROUP BY item.name, MONTH(outgoing.date)
ORDER BY item.name, outgoing.date ASC
that query result is like..
Product A month 2 : 24
Product A month 3 : 7
Product B month 1 : 8
ect..
but thats not what i want. i want the query have a result like this ..
Product A {
month 1 : 13
month 2 : 24
month 3 : 7
},
Product B {
month 1 : 8
month 2 : 11
month 3 : 22
month 4 : 20
},
Product C {
month 1 : 4
month 2 : 8
}
how should i change the query so the result can be like i want? P.S. : can u make the query builder translation for it?
@karrura Is that works?
SELECT item.name as Produk,
DATE_FORMAT(outgoing.date, '%m') as Month,
SUM(outgoing_item.quantity) as Jumlah
FROM outgoing, outgoing_item, outgoing_type, item
WHERE item.id = outgoing_item.item_id
AND outgoing_item.outgoing_id = outgoing.id
AND outgoing_type.id = outgoing.outgoing_type_id
AND outgoing_type_id = 1
GROUP BY item.name, Month
ORDER BY item.name, Month ASC
@tisuchi that query same as beginning, when i run it in sql phpmyadmin, the result will like the first one, when i translate it to query builder and do dd($query), the result ..
#items: array:5 [▼
0 => {#1386 ▼
+"name": "ALL IN BULAT (6TIN) 235GR"
+"tanggal": "2022-01-15"
+"month": 1
+"sum(outgoing_item.quantity)": "182"
}
1 => {#1388 ▼
+"name": "ALL IN BULAT (6TIN) 235GR"
+"tanggal": "2022-02-01"
+"month": 2
+"sum(outgoing_item.quantity)": "200"
}
2 => {#1389 ▼
+"name": "ALL IN BULAT (6TIN) 235GR"
+"tanggal": "2022-03-03"
+"month": 3
+"sum(outgoing_item.quantity)": "345"
}
But i want this..
Product A {
month 1 : 13
month 2 : 24
month 3 : 7
},
Product B {
month 1 : 8
month 2 : 11
month 3 : 22
month 4 : 20
},
Product C {
month 1 : 4
month 2 : 8
}
Yes, I also recently had a problem I could not find a company that deals with garage doors. And I needed it urgently, because they were so broken that they did not even open. And then I accidentally came across the company https://garagedoorrepairbc.ca/ . Was really pleased with their quick and high quality work. As for me they did everything quickly and without any problems. I am very happy with the service. I recommend it to everyone.
It is not possible to format the result as you described using just SQL. And if you're using Laravel, then you should be using Eloquent or at least query builder anyway.
Here is the query builder equivalent of the SQL query you provided:
$results = DB::table('outgoing')
->select('item.name as Produk', 'outgoing.date as Tanggal', DB::raw('sum(outgoing_item.quantity) as Jumlah'))
->join('outgoing_item', 'item.id', '=', 'outgoing_item.item_id')
->join('outgoing_type', 'outgoing_type.id', '=', 'outgoing.outgoing_type_id')
->join('item', 'outgoing_item.outgoing_id', '=', 'outgoing.id')
->where('outgoing_type_id', 1)
->groupBy(['item.name', DB::raw('MONTH(outgoing.date)')])
->orderBy('item.name', 'outgoing.date', 'ASC')
->get();
This will return a collection that you can then manipulate more easily using Laravel's collection methods.
@lbecket here is the result after i do minor correction to make it work
#items: array:5 [▼
0 => {#1387 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-01-15"
+"Jumlah": "182"
}
1 => {#1386 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-02-01"
+"Jumlah": "200"
}
2 => {#1389 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-03-03"
+"Jumlah": "345"
}
3 => {#1391 ▶}
4 => {#1388 ▶}
]
is there a way to make a result like this..?
0 => {#1387 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
array{
"month 1":"182"
"month 2":"200"
"month 3":"345"
}
}
@Karrura You should be able to get that result by using the groupBy collection method on the query builder results. You can read more about it here: https://laravel.com/docs/master/collections#method-groupby
@lbecket Ahh! thanks for attach the link. I just read it and try it. This is the result in dd(), and yes that is what i want.
#items: array:5 [▼
"ALL IN BULAT (6TIN) 235GR" => Illuminate\Support\Collection {#1383 ▼
#items: array:4 [▼
0 => {#1387 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-01-15"
+"Jumlah": "182"
}
1 => {#1386 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-02-01"
+"Jumlah": "200"
}
2 => {#1389 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-03-03"
+"Jumlah": "345"
}
3 => {#1391 ▼
+"Produk": "ALL IN BULAT (6TIN) 235GR"
+"Tanggal": "2022-04-07"
+"Jumlah": "20"
}
]
#escapeWhenCastingToString: false
}
"ALL IN SEGI NEW (6TIN) 600GR" => Illuminate\Support\Collection {#1381 ▶}
"ASTOR EMBER (7 X 30 PCS)" => Illuminate\Support\Collection {#1380 ▶}
"ASTOR MINI TRUST COKLAT 10 PACK" => Illuminate\Support\Collection {#1379 ▶}
"ASTOR MINI TRUST PANDAN (10 PACK)" => Illuminate\Support\Collection {#1382 ▶}
]
But the result become different after i bring it to the blade
$data = $results->groupBy('Produk');
return DataTables::of($data)->make(true);
when i do console.log(), this is the result
{Produk: 'ALL IN BULAT (6TIN) 235GR', Tanggal: '2022-01-15', Jumlah: '182'}
Jumlah: "182"
Produk: "ALL IN BULAT (6TIN) 235GR"
Tanggal: "2022-01-15"
[[Prototype]]: Object
{Produk: 'ALL IN SEGI NEW (6TIN) 600GR', Tanggal: '2022-01-17', Jumlah: '41'}
{Produk: 'ASTOR EMBER (7 X 30 PCS)', Tanggal: '2021-02-05', Jumlah: '13'}
{Produk: 'ASTOR MINI TRUST COKLAT 10 PACK', Tanggal: '2021-02-05', Jumlah: '880'}
thats different when i do dd($data), or is there something i missed?
@Karrura I'm not particularly familiar with DataTables, but it would seem that this is what's transforming your $data. If you are getting the query results into the desired shape as $data, why are you not returning that directly?
@lbecket i cant return it directly, i need to use server side datatables since there are more than thousand row data. If i keep return it directly, it'll make the browser freezing and not responding :') But, at least i can grouped the data in controller now, so i just need to move the rest of my math process there instead of doing it in some js function in the blade :D
@Karrura In short no, there is no good way to get that kind of result with SQL.
It is possible to get it like that, but the query would be a mess.
You would need to use concat and groupconcat to generate the json like mont 1, month 2 groupings.
I use a similar techique to get all the authors for a book here.
@Karrura Well, it still seems that the DataTables are transforming the data in a manner that is undesirable. Collections don't have a paginate method, but that's effectively what you need. My understanding is that the forPage method can effectively achieve that same result by retrieving a portion of the collection based on the desired page number. You can read more about that here: https://laravel.com/docs/master/collections#method-forpage
Please or to participate in this conversation.