Group single Eloquent query by date

I have a table with records that have 'runs_at' dates. In the dashboard of the user I'd like to distinguish between records that are upcoming (date >= now) or records that are in the past (date < now).

Ideally though I'd to get the results back in a single array so I can loop through them (nested) in the front-end.

[
    'upcoming' =>
    [
        0 => 'Record 1',
        1 => 'Record 3',
        2 => 'Record 4',
    ],
    'past' =>
    [
        0 => 'Record 2',
    ],
]

What confuses me now is that I am using a step-wise query builder, due to the complexity of the where's and such. So basically now it looks something like (called via VueJS as API):

$records = $records->newQuery();
$records->where('user_id', auth()->id())
$records->where('active', true)
// (cont'd)

return RecordsResource::collection($records->get());

I feel I need a fresh set of eyes. I've started duplicating code (which can never be the answer), added a calculated property (is_past = true), which I can't work out in a loop in the front-end and more. Might be super simple. But I'm not seeing it.

Sinnbeck

6 years ago

Best Answer

Level 102

Well first of all your resource collection will expect to get a one dimentional array (or collection) with the data. That means that it will not work if you pass in the grouped records.

You should be able to do something like this.

$records = $records->newQuery();
$records->where('user_id', auth()->id())
$records->where('active', true)
$allRecords = $records->get();
$groupedRecords = $allRecords->groupBy(function($item) {
    return $item->date->isPast() ? 'past' : 'upcoming';
});
return [
    'upcoming' => RecordsResource::collection($groupedRecords['upcoming']),
    'past' => RecordsResource::collection($groupedRecords['past']),
];

axtg

6 years ago

Level 5

That was fast. Awesome, thanks. I was trying to manipulate the $records directly (before get()). That was, like you said, never going to work.

Please or to participate in this conversation.