ihue February 2016

Get latest distinct value of a filed 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

tried update my

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

I got the same result.


Try #3

tried placing orderBy after groupBy

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

I got the same result.


Any hints / suggestions on that will much appreciated !

Answers


Fabio Antunes February 2016

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

Post Status

Asked in February 2016
Viewed 3,865 times
Voted 14
Answered 1 times

Search




Leave an answer