Level 63
Not sure, but have you tried without () after users ?
return $room->users // MODIFICATION HERE
...
1 like
Sep 6, 2022
8
Level 1
How to get the same data for more users?
Hi, I have code like this that works fine:
return $user->statistics()
->selectRaw('created_at as date, MIN(level) as min_level, max(level) as max_level, SUM(level_change) as sum_level_change')
->whereDate('created_at', '>=', now()->subDays(7))
->groupByRaw('DAY(date)')
->latest()
->get();
[
{
"date": "2022-09-05 17:23:22",
"min_level": 2748,
"max_level": 2750,
"sum_level_change": "3"
},
{
"date": "2022-09-04 11:20:17",
"min_level": 2722,
"max_level": 2747,
"sum_level_change": "114"
},
{
"date": "2022-09-03 11:14:19",
"min_level": 2715,
"max_level": 2721,
"sum_level_change": "31"
},
{
"date": "2022-09-02 20:32:17",
"min_level": 2712,
"max_level": 2714,
"sum_level_change": "15"
},
{
"date": "2022-09-01 19:32:19",
"min_level": 2709,
"max_level": 2710,
"sum_level_change": "7"
},
{
"date": "2022-08-31 19:02:19",
"min_level": 2703,
"max_level": 2708,
"sum_level_change": "41"
},
{
"date": "2022-08-30 20:26:19",
"min_level": 2701,
"max_level": 2702,
"sum_level_change": "8"
}
]
However, I would also like to use this for more users. This is one of my attempts, but blank data is returned
return $room->users()
->with('statistics', function ($query) {
$query->selectRaw('created_at as date, MIN(level) as min_level, max(level) as max_level, SUM(level_change) as sum_level_change');
$query->whereDate('created_at', '>=', now()->subDays(7));
$query->groupByRaw('DAY(date)');
})
->limit(25)
->get();
[
{
"id": 1,
"room_id": 65,
"name": "XXX",
"level": 1012,
"created_at": "2022-07-25T18:04:04.000000Z",
"updated_at": "2022-09-06T03:29:23.000000Z",
"statistics": []
},
{
"id": 2,
"room_id": 65,
"name": "XXX",
"level": 6,
"created_at": "2022-09-03T19:32:19.000000Z",
"updated_at": "2022-09-03T19:38:19.000000Z",
"statistics": []
},
(...)
]
Could someone help me? I would like statistics to be assigned the same data as for one user and to be sorted according to the first array by sum_level_change.
šš
Level 1
@vincent15000 Unfortunately, that's not it :(
Method Illuminate\Database\Eloquent\Collection::with does not exist.
1 like
Level 63
@mbaq55 Sorry ... it was not the solution. I often make a confusion between the relationships with and without () when trying to retrieve them.
Level 1
try this
return $room->with('users', function($query){
$query ->with('statistics', function ($query) {
$query->selectRaw('created_at as date, MIN(level) as min_level, max(level) as max_level, SUM(level_change) as sum_level_change');
$query->whereDate('created_at', '>=', now()->subDays(7));
$query->groupByRaw('DAY(date)');
})->limit(25)->get()
})->get();
2 likes
Level 1
@heshamHanafi Thanks for your willingness to help, but I get an error.
Method name must be a string
1 like
Level 6
you have to select the user_id too because it's the foreign key
$query ->with(['statistics' => function ($query) {
$query->selectRaw('created_at as date, MIN(level) as min_level, max(level) as max_level, SUM(level_change) as sum_level_change, user_id, id');
$query->whereDate('created_at', '>=', now()->subDays(7));
$query->groupByRaw('DAY(date)');
}]);
2 likes
Level 1
@khaledw62 Thank you very much! š¤
1 like
Level 6
@mbaq55 you're very welcome
1 like
Please or to participate in this conversation.