Level 73
I suggest you do the in your database query instead of mapping over the result with php.
So if you supply some sample data and the expected result we might be able to help you.
Jul 13, 2021
3
Level 2
GroupBy and returning object instead of key
Hello, I want to group by a collection by value without setting the key as the key (:D) , Edited: My tables structures:
-Users(id,firstname,lastname,...)
-Events(id,type,fromDate,endDate,...)
-Sport(id,event_id,nbMax,coverImg,...) -> event_id references events(id)
-Challenge(id,event_id,nbMax,address,...) -> event_id references events(id)
-Sensitization(id,event_id,patient...) -> event_id references events(id)
-Registrations(id,event_id,user_id)
My objective is to calculate the percentage of registrations for each type of event compared to nbMax in a given year(for the event start), I did:
Event::whereIn('type', $request->filter)
->select('events.id', 'events.type', 'events.fromDate')
->whereYear('events.fromDate', $request->year)
->orderBy('events.fromDate')
->withCount('registration')
->get()
->map(function ($query) {
$event = $query->Sport ?: ($query->Challenge ?: 0);
$query->nbMax = $event ? $event->nbMax : 1;
return $query;
});
returning :
[
{
"id": 115,
"type": 3,
"fromDate": "2021-05-27 00:00:00",
"registration_count": 4,
"nbMax": 10
},
{
"id": 117,
"type": 3,
"fromDate": "2021-05-27 00:00:00",
"registration_count": 7,
"nbMax": 12
},
{
"id": 120,
"type": 4,
"fromDate": "2021-05-30 00:00:00",
"registration_count": 1,
"nbMax": 10
},
{
"id": 121,
"type": 4,
"fromDate": "2021-05-30 00:00:00",
"registration_count": 3,
"nbMax": 10
}
]
I grouped this data by type then by fromDate, This is how I did :
$filterResult = $result->groupBy(['type', function ($item) {
return Carbon::parse($item->fromDate)->format('Y-m-d');
}])
I got this :
{
"3": {
"2021-05-27": [
{
"id": 115,
"type": 3,
"fromDate": "2021-05-27 00:00:00",
"registration_count": 4,
"nbMax": 10
},
{
"id": 117,
"type": 3,
"fromDate": "2021-05-27 00:00:00",
"registration_count": 7,
"nbMax": 12
}
]
},
"4":{
"2021-05-30":[
{
"id": 120,
"type": 4,
"fromDate": "2021-05-27 00:00:00",
"registration_count": 4,
"nbMax": 10
},
{
"id": 121,
"type": 4,
"fromDate": "2021-05-27 00:00:00",
"registration_count": 7,
"nbMax": 12
},
]
}
}
then I did added a map like this :
->map(function ($query) {
return $query->map(function ($item) {
return (collect($item)->sum('registration_count') * 100) / collect($item)->sum('nbMax');
});
});
and i got like this:
{
"3": {
"2021-05-27": 3.125
},
"4": {
"2021-05-30": 3.125
},
}
But the output i want is far away from the result :) :
{
"3": [
{
"fromDate":"2021-05-27",
"sum":3.125
}
],
"4": [
{
"fromDate":"2021-05-30",
"sum":3.125
}
]
}
Level 2
Thank for your reply , Maybe the raison I didn't worked with database query is that I have a map to get the structure before groupBy. The structures of my tables are :
-Users(id,firstname,lastname,...)
-Events(id,type,fromDate,endDate,...)
-Sport(id,event_id,nbMax,coverImg,...) -> event_id references events(id)
-Challenge(id,event_id,nbMax,address,...) -> event_id references events(id)
-Sensitization(id,event_id,patient...) -> event_id references events(id)
-Registrations(id,event_id,user_id)
The raison i used different tables Sport, Challenge, Sensitization( i have 4 more tables like this) and referencing events table is that each table has it's own columns. My objective is to calculate the percentage of registrations for each type of event compared to nbMax in a given year(for the event start), So I did :
Event::whereIn('type', $request->filter)
->select('events.id', 'events.type', 'events.fromDate')
->whereYear('events.fromDate', $request->year)
->orderBy('events.fromDate')
->withCount('registration')
->get()
->map(function ($query) {
$event = $query->Sport ?: ($query->Challenge ?: 0);
$query->nbMax = $event ? $event->nbMax : 1;
return $query;
});
The nbMax is in the child table so I map the result to extract it to the main object. I edited the main post to include the desired output.
Level 2
What I wanted in the final output is to group by without setting the key and returning and object instead of a simple value so I changed the map To:
->map(function ($query) {
return $query->map(function ($item) {
return [
"date"=>Carbon::parse($item[0]->fromDate)->translatedFormat('Y-m-d'),
"sum"=>(collect($item)->sum('registration_count') * 100) / collect($item)->sum('nbMax')
];
});
});
I got this result :
{
"3": {
"2021-05-27":
{
"fromDate":"2021-05-27",
"sum":3.125
}
},
"4": {
"2021-05-30":
{
"fromDate":"2021-05-30",
"sum":3.125
}
}
}
Then I added ->values()->toArray(); after the map so the result become as expected:
{
"3": [
{
"fromDate":"2021-05-27",
"sum":3.125
}
],
"4": [
{
"fromDate":"2021-05-30",
"sum":3.125
}
]
}
Please or to participate in this conversation.