CODEWITHSUNDEEP
phplaravellaravel-5
code-8
code-8

Reputation: 58642

Get latest distinct value of a field in Laravel

I want to query the latest + distinct name.

I got the distinct part to work, but they're not the latest. I'm not sure how to do that in Laravel.

I’ve tried

$localDevices = Device::orderBy('created_at', 'desc')->groupBy('mac')->get();

        $localDeviceName = [];
        $i = 0;

        foreach ($localDevices as $localDevice) {
            foreach ($devices as $device) {
                if($localDevice->mac == $device->device_mac ){
                    $localDeviceName[$i]['name'] = $localDevice->name;
                    $localDeviceName[$i]['mac'] = $device->device_mac;
                    $i++;
                }
            }
        }

Database

enter image description here

I got

array:1 [▼
  0 => array:3 [▼
    "name" => "Apple Watch"
    "img" => "/images/photos/devices/apple-watch.jpg"
    "mac" => "080027E2FC7D"
  ]
]

I want it to show ps4 because it is the latest.

Try #2

I tried update my

orderBy('created_at', 'desc') to orderBy('created_at', 'asc')

I got the same result.

Try #3

I tried placing orderBy after groupBy

Device::groupBy('mac')->orderBy('created_at', 'desc')->get();

I got the same result.

What can I try next?

Upvotes: 3

Views: 890

Answers (1)

Fabio Antunes
Fabio Antunes

Reputation: 22862

You are doing a groupBy on your mac value which isn't unique, your Apple watch and PS4 have the same mac, mysql first groups by then orders your grouped results. That's why you are always getting Apple watch.

What you want is to fetch the latest record from each group and for that you might write a Raw query, check this Retrieving the last record in each group

Upvotes: 2

Related Questions