How to Group by data by Day, week or month

on PHP Level

Borrower::activeBorrowers()
->with('owners')
->with(['transactions' => function ($query) use($from,$to,$request) {
        $query->where('action', $request->filter)
          ->whereDate('trans_transactions_details.created_at','>=',$from)
          ->whereDate('trans_transactions_details.created_at','<=',$to)
          ->groupBy(function($query) {
                     
                    //return Carbon::parse($data->created_at)->format('Y-m-d');
                   
                  //return Carbon::parse($data->created_at)->format('Y-m');
                 
                  return Carbon::parse($query->created_at)->format('W');
            });
        }])
 ->orderBy('legal_business_name')
 ->get();

Tried like this but it's not working. I'm using laravel 8

You need to format the code, it is not readable like this.

Sorry, It's formatted now

Try following:

Borrower::activeBorrowers()
->with('owners')
->with(['transactions' => function ($query) use($from,$to,$request) {
        $query->where('action', $request->filter)
          ->whereDate('trans_transactions_details.created_at','>=',$from)
          ->whereDate('trans_transactions_details.created_at','<=',$to)
          ->groupByRaw('WEEK(trans_transactions_details.created_at)'); // this is when you need week
	  ->groupByRaw('DATE_FORMAT(trans_transactions_details.created_at, "%Y%m")'); // this is when you need month
	  ->groupByRaw('DATE_FORMAT(trans_transactions_details.created_at, "%Y%m%d")'); // this is when you need day
        }])
 ->orderBy('legal_business_name')
 ->get();

It's not working returns a Syntax error

SQLSTATE[42000]: Syntax error or access violation: 1055 'midtown_capital.trans_transactions_details.id' isn't in GROUP BY (SQL: select `trans_transactions_details`.*, `finances`.`borrower_id` as `laravel_through_key` from `trans_transactions_details` inner join `finances` on `finances`.`id` = `trans_transactions_details`.`finance_id` where `finances`.`borrower_id` in (100d2b65-3cf6-4f7e-9643-d7f810f2f6d2, 22200aa7-67e2-4a1f-b256-f83a34fbcb52) and `action` = banking_fee and date(`trans_transactions_details`.`created_at`) >= 2020-12-18 and date(`trans_transactions_details`.`created_at`) <= 2020-12-24 group by WEEK(trans_transactions_details.created_at))

What if you add select?

Borrower::activeBorrowers()
->with('owners')
->with(['transactions' => function ($query) use($from,$to,$request) {
        $query
	  ->select('id')
	  ->where('action', $request->filter)
          ->whereDate('trans_transactions_details.created_at','>=',$from)
          ->whereDate('trans_transactions_details.created_at','<=',$to)
          ->groupByRaw('WEEK(trans_transactions_details.created_at)'); // this is when you need week
	  ->groupByRaw('DATE_FORMAT(trans_transactions_details.created_at, "%Y%m")'); // this is when you need month
	  ->groupByRaw('DATE_FORMAT(trans_transactions_details.created_at, "%Y%m%d")'); // this is when you need day
        }])
 ->orderBy('legal_business_name')
 ->get();

No it's returns a same error

What i want to do is like i need that all data with sum of the transaction amount grouped by day, week or month

something like this

{[
	'Owners' => [],
	'transactions' => [
		// if group by week then all transactions occurs in particular week of given date range with total amount 
                    of these all transactions
          ]
  ]
}

is this something possible to do so with single call or else i can do two different call but having issue in group by

I see that you are using UUID. Are you sure everything is working correctly cause of that change? I know that for older versions you would have to change strict to false in database.php file. But try this also:

Borrower::activeBorrowers()
->selectRaw('`trans_transactions_details`.*,`finances`.`borrower_id` AS `laravel_through_key`, WEEK(trans_transactions_details.created_at) AS createdAtWeek')
->with('owners')
->with(['transactions' => function ($query) use($from,$to,$request) {
        $query
	  ->where('action', $request->filter)
          ->whereDate('trans_transactions_details.created_at','>=',$from)
          ->whereDate('trans_transactions_details.created_at','<=',$to)
          ->groupByRaw('createdAtWeek'); // this is when you need week
        }])
 ->orderBy('legal_business_name')
 ->get();

there is an error

SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'midtown_capital.trans_transactions_details' (SQL: select `id`, `mislinked`, `legal_business_name`, `created_at`, `trans_transactions_details`.*,`finances`.`borrower_id` AS `laravel_through_key`, WEEK(trans_transactions_details.created_at) AS createdAtWeek from `borrowers` where exists (select * from `finances` where `borrowers`.`id` = `finances`.`borrower_id` and `declined` is null and `archived` is null and (`status` = 61 or `status` = 62)) order by `legal_business_name` asc)

trans_transactions_details is a foreign table for Borrower

Hard to figure that out from the browser.

is there any other solution for that?

There is but can not figure it out from mobile phone screen. Very hard to read.

I can wait until you have access to your computer.

I just want a solution because I’m stuck in this issue.

Just let me know whenever you have access to computer

Thanks

Do you know how to write MySQL for this? Cause it would be hard for me to guess how you've configured relationships and everything.