CODEWITHSUNDEEP
phplaravelcollections
Dazzle
Dazzle

Reputation: 59

How to get the last one data from each collection in Laravel

I want to get only one latest data sorted by created_at from each collection,

this is my code now :

$getDevice = Devices::where('id_project', $id_project)
                          ->where('id_room', $id_room)
                          ->get()
                          ->groupBy(function($item){
                              return $item->mac_address;
                          });

 dd($getDevice);

the output:

output

Thank you.

Upvotes: 0

Views: 517

Answers (3)

TsaiKoga
TsaiKoga

Reputation: 13394

You cannot order data in group for mysql, so you can do it like this:

FOR MYSQL WAY:

AUTO INCREMENT ID:

The latest data from each collection means the max id in each collection.

So you can find all the max id in each group, and use subquery to get all these records:

$getDevice = Devices::whereIn('id', function($q) use ($id_project, $id_room) {
                          $q->from('devices')
                            ->where('id_project', $id_project)
                            ->where('id_room', $id_room)
                            ->groupBy('mac_address')
                            ->selectRaw('MAX(id) AS id');
                      })->get();
UUID:

Because uuid is a unordered string, you can join by mac_address and max created_at. And group by mac_address again, so it will get the latest data

$deviceQuery = Devices::where('id_project', $id_project)
                      ->where('id_room', $id_room)
                      ->groupBy('mac_address')
                      ->select('mac_address', 'MAX(created_at) AS created_at');


$getDevice = Devices::joinSub($deviceQuery, 'dev', function($join) {
                          $join->on('dev.mac_address', '=', 'devices.mac_address')
                               ->whereColumn('dev.created_at', 'devices.created_at');
                      })->groupBy('mac_address')
                        ->get();

For Collection way:

Collection will be more readable, however, it need to takes the data out from DB, and then filter to get the last data in group.

$getDevice = Devices::where('id_project', $id_project)
                          ->where('id_room', $id_room)
                          ->get()
                          ->groupBy('mac_address')
                          ->map(function($items) {
                              return $items->sortByDesc('mac_address')->first();
                          })->values();

Upvotes: 1

devhubpl
devhubpl

Reputation: 11

You can use Devices::latest()->first(); So you can build query with this example:

Devices::where('id_project', $id_project)
          ->where('id_room', $id_room)
          ->latest()->first();

Upvotes: 0

Rafael L R
Rafael L R

Reputation: 530

Try $model->orderBy('column', 'desc')->first();

Upvotes: 0

Related Questions