0

i want to regroup my data by week then by days of week . this is my code 

$pp=Path::join('reservations', function($join){
$join->on('reservations.path_id', '=', 'paths.id')  
->where('reservations.state','=',1);})
->where('paths.user_id','=',Auth::user()->id)
->whereBetween('departure_date', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
->get()
->groupBy(function($date)  {
return Carbon::parse($date->departure_date)->format('d'); // grouping by days
})->toArray();

this is my output my output but I would like for the results to be presented like 

Monday => some data ..
Tuesday => some data ..
Wednesday => some data ...
ilyés Tabessi
  • 47
  • 1
  • 7

1 Answers1

0

use mysql DateTimeFunctions:

->groupBy(DB::raw('DAYNAME(departure_date)')

you can use: (DAYOFMONTH) to return the day order in month

or DAYOFWEEK to return the day order in week, or DAYOFYEAR to return the day order in year,

and lot of option to group by day format

edit:

to add an alias:

->select('*',DB::raw('DAYNAME(departure_date) as weekDay')
    ->groupBy(DB::raw('weekDay'))->get();
OMR
  • 11,736
  • 5
  • 20
  • 35
  • my result become like picture below did can i change this scaping by some alias ? look picture https://image.noelshack.com/fichiers/2020/23/3/1591178143-week.png – ilyés Tabessi Jun 03 '20 at 09:58
  • my code become like this : https://image.noelshack.com/fichiers/2020/23/3/1591179494-code.png but i had this problem : https://image.noelshack.com/fichiers/2020/23/3/1591179401-prob.png did u have any solution ? – ilyés Tabessi Jun 03 '20 at 10:19
  • you have two problems here 1- ->groupBy(DB::raw('weekDay')) not ->groupBy(DB::raw('DAYNAME(departure_date)') 2- get() must comes at the end of query ... not before – OMR Jun 03 '20 at 10:39
  • i changed it to : https://image.noelshack.com/fichiers/2020/23/3/1591183619-codee.png but i had this error : https://image.noelshack.com/fichiers/2020/23/3/1591183421-error.png – ilyés Tabessi Jun 03 '20 at 11:28
  • you must do this: https://stackoverflow.com/a/49230235/10573560 – OMR Jun 03 '20 at 11:33
  • okey sir i'ts fine now thnx , in my case i want to multiplied the price of path by the reservation accepted .. for example : i have in this week just 1 path created at wednesday, his price 15 and i have two reservations accepted i want my result become "Monday => 0" "Tuesday=> 0" "Wednesday => 15*2 " etc .. how i can do it ? – ilyés Tabessi Jun 03 '20 at 12:09
  • in your select... add DB::raw('mytable.myColumn * myTable.myColumne as myAlias') – OMR Jun 03 '20 at 12:12
  • DB::raw('') allows you to write pure sql – OMR Jun 03 '20 at 12:13
  • something like >select('*',DB::raw('DAYNAME(departure_date) as weekDay', DB::raw('(reservations.accepted * paths.price) as price')) – OMR Jun 03 '20 at 12:18
  • i optimize my code : https://image.noelshack.com/fichiers/2020/23/3/1591197145-aftero.png ... to get this result : https://image.noelshack.com/fichiers/2020/23/3/1591197258-afterop.png but the problem is the data returned is just for the first path .. for example in my case i have two paths on saturday but the result returned is just for the first path on saturday .. did you have any solution to get all the paths per day ? – ilyés Tabessi Jun 03 '20 at 15:19
  • sure ... add paths.id to select and to group statements – OMR Jun 03 '20 at 15:24
  • i add thnx sir .. my output become : https://image.noelshack.com/fichiers/2020/23/3/1591200804-result.png can you give me solution to get this output { "id": 41, "weekDay": "Friday", "revenus": 17 }, { "id": 1, "weekDay": "Saturday", "revenus": 13+16+30 } , – ilyés Tabessi Jun 03 '20 at 16:18
  • sure ... remove path.id from select, also remove your computed column, replace it with: DB::raw('SUM(paths.price * paths.placed_approved) as sumRevenus') – OMR Jun 03 '20 at 17:43
  • great thnx sir :) – ilyés Tabessi Jun 03 '20 at 18:20
  • the last thing sir , how can i have this result { "weekDay": "Monday", "sumRevenus": 0 }, { "weekDay": "Tuesday", "sumRevenus": 0 }, { "weekDay": "Wednesday", "sumRevenus": 0 },{"weekDay": "Friday", "sumRevenus": 17 }, etc... even the days that are not have path presented with 0 in sumRevenus ? – ilyés Tabessi Jun 03 '20 at 18:36
  • there in no easy way to do that ... but nothing is impossible ...i 'am sure you will enjoy solving it ... the base idea is to make constructed table as sub query and join it with your qeury! the constructed table shall have the names of the weekDays – OMR Jun 03 '20 at 19:18
  • here how to join sub query https://laravel.com/docs/7.x/queries#joins see Subquery Joins – OMR Jun 03 '20 at 19:19
  • and here how you make constructed table: https://stackoverflow.com/a/48400927/10573560 – OMR Jun 03 '20 at 19:20
  • did you mean i must create real a table in my BD contains my weekDays or just a virtual table ? – ilyés Tabessi Jun 03 '20 at 22:46
  • of course not ... it much like sub query with in memory table , constructed tables are meant to use for short join like joinnig with day week names – OMR Jun 04 '20 at 06:47
  • did you make it? – OMR Jun 22 '20 at 09:24