OrderBy and groupBy with another orderBy

Hi.

I have a collection that has orders the results by ->latest() and then it is grouped by date.

This all works but within the date group, I want to order it by oldest first rather than newest.

I'm the database table I do have and order column with the correct order.

I.e.

// How id like it

6th November
Scan 1 
Scan 2
Scan 3

4th November
Scan 1 
Scan 2
Scan 3

2nd November
Scan 1 
Scan 2
Scan 3

// How it is now
6th November
Scan 3 
Scan 2
Scan 1

4th November
Scan 3 
Scan 2
Scan 1

2nd November
Scan 3 
Scan 2
Scan 1

The controller looks like so::

public function index()
    {
        $collection = CustomerScan::where('user_id', Auth::id())
        ->latest()
        ->get()
        ->groupBy(function ($item) {
            return $item->created_at->format('Y-m-d');
        });

        return view('customer.scan-viewer.index')->with([
            'collection' => $collection,
        ]);
    }

View::

@foreach ($collection as $date => $scans)
	<hr style="border-top: 1px #ccc;">
	<span>
		<strong>{{ Carbon\Carbon::parse($date)->format('jS F Y') }}</strong>
	</span>
	<hr style="border-top: 1px #ccc;">

	@foreach($scans->chunk(3) as $chunk)
		@foreach($chunk as $scan)
			<!-- MY CODE --> 
                @endforeach
 	@endforeach

@endforeach

Any help would be greatful.

Cheers

Mirlan Bekturov

5 years ago

Level 1

Hi! You can use sortBy() method for collection. For example:

@foreach($chunk->sortBy('name') as $scan)
	<!-- MY CODE --> 
 @endforeach

1 like

rodrigo.pedra

5 years ago

Best Answer

Level 56

You can call the collection sort by in the @\foreach

@foreach($chunk->sortBy('order') as $scan)

But I think if you get the results sorted from the DB group by would preserve its sorting

public function index()
    {
        $collection = CustomerScan::where('user_id', Auth::id())
        ->orderByRaw('CAST(created_at AS DATE) DESC') // order by future grouped by
        ->orderBy('order') // order within each group
        ->get()
        ->groupBy(function ($item) {
            return $item->created_at->format('Y-m-d');
        });

        return view('customer.scan-viewer.index')->with([
            'collection' => $collection,
        ]);
    }

But depending on how many records you have, using a CAST(...) within your order by clause can decrease your query's performance as it would not use any indices on that column.

skoobi

5 years ago

Level 13

Adding

->orderByRaw('CAST(created_at AS DATE) DESC') // order by future grouped by
->orderBy('order') // order within each group

That worked perfectly.

Thanks all for your help

1 like

rodrigo.pedra

5 years ago

Level 56

You're welcome! Have a nice day =)

Mirlan Bekturov

5 years ago

Level 1

Or you can use this version:

 $collection = CustomerScan::where('user_id', Auth::id())
        ->latest()
        ->get()
        ->groupBy(function ($item) {
            return $item->created_at->format('Y-m-d');
        })->map(function ($item) {
               return $item->sortBy('name');
         });

1 like

Please or to participate in this conversation.