Good Afternoon... I wanted to get the data from database which will be groupby customerID and base on same data in below format :

Refer attached images for my database. I able to get data groupby customerID but stuck for futher details.

$test = Dairyincome::get()->groupBy('customerID')->toArray();
dump($test);

Expected result

same for other ID

Hope i explained my problem and thanks in Advance

CodePudding user response：

I don't recommend using collection (you use ->groupBy() after ->get()).

You need to know how it can work using SQL first, before using Eloquent or the Query Builder.

I assume your table is dairyincomes :

SELECT
  customerID, 
  MIN(date) as "From date", 
  MAX(date) as "To Date",
  SUM(quantity) as "Total Quantity",
  SUM(amount) as "Total Amount"
FROM
  dairyincomes
GROUP BY
  customerID

• The MIN() function returns the minimum value in a set of values.
• The MAX() function returns the maximum value in a set of values.
• The SUM() function is an aggregate function that allows you to calculate the sum of values in a set.

Eloquent :

Dairyincome::selectRaw('customerID, MIN(date) as "From date", MAX(date) as "To Date", SUM(quantity) as "Total Quantity", SUM(amount) as "Total Amount"')
    ->groupBy('customerID')
    ->get();

Also, you can use DB::raw() btw.

Reference :

• https://laravel.com/docs/9.x/queries