3

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
code-8
  • 54,650
  • 106
  • 352
  • 604
  • Shouldn't the second argument in `orderBy()` should be `desc` not `dsc`? – camelCase Feb 08 '16 at 16:34
  • Same result even change it to `orderBy('created_at', 'desc')`. I'll update my post. – code-8 Feb 08 '16 at 16:35
  • Little odd, your approach is correct, it should be working. When I use `orderBy('created_at', 'desc')` in my queries, the expected result is given. How about removing the `groupBy()` constraint all together, does it order correctly? – camelCase Feb 08 '16 at 16:50

1 Answers1

2

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

Community
  • 1
  • 1
Fabio Antunes
  • 22,251
  • 15
  • 81
  • 96