laravel 5.3 - order results via whereIn mysql

Question

I have looked at most other solutions here but none of them seems to work for me. I have a table with about 100K rows in it.

each row is a record from an iot device, which includes the unique id of that device. The device id may have 10K rows from the 100K.

Reading::whereIn('device_id', $ids)->orderBy('created_at',
 'DESC')->groupBy('device_id')->get()->

I am able to pull out the ids i need and group them together by "device_id" as query above but its picking the first result and not the last for each id.

How can i get the most recent record for each id instead of the oldest?

I have looked at laravel collections and the reverse(), first(), latest(); option but i still cant get it

Any ideas?

UPDATE

The best i have been able to come up with is this query below which does grab the latest record for each device_id

SELECT t1.* FROM readings t1 WHERE t1.id = (SELECT t2.id FROM readings
t2 WHERE t2.device_id = t1.device_id ORDER BY t2.id DESC LIMIT 1)

I'll then loop through those results and grab only the records the user has access to.

Not pretty, but it works for now.

If anyone ever does find away to do it in eloquent please answer

nibnut · Answer 1 · 2017-01-13T21:41:24.953

0

I believe this should work?

Reading::select('device_id', 'id', DB::raw('MAX(created_at) AS latest'))->whereIn('device_id', $ids)->orderBy('latest', 'DESC')->groupBy('device_id')->get();

Hope this helps!

edited Jan 13 '17 at 21:41

answered Jan 13 '17 at 20:44

nibnut

3,072
2
15
17

i get this "ErrorException in Grammar.php line 58: strtolower() expects parameter 1 to be string, object given" – ATechGuy Jan 13 '17 at 20:46
you probably passed in the instance of an object... show us the code – GabMic Jan 13 '17 at 20:48
which code eitan? my code is above, the $ids is an array – ATechGuy Jan 13 '17 at 20:50
and what's on line 58 of Grammar.php? – nibnut Jan 13 '17 at 20:55
its part of laravels default install, i have looked this error up and it happens alot in laravel apparently. Trying to find a fix. example: https://laracasts.com/discuss/channels/eloquent/query-builder-strtolower-error – ATechGuy Jan 13 '17 at 21:04
getting close lol "Can't group on 'latest' (SQL: select `device_id`, MAX(created_at) as latest from `readings` where `device_id` in (122334, 1122334455) group by `latest`)" – ATechGuy Jan 13 '17 at 21:18
Doh! Going too fast, sorry. Of course you want to group on device_id - updated my answer... – nibnut Jan 13 '17 at 21:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133116/discussion-between-keaner-and-macprawn). – ATechGuy Jan 13 '17 at 21:25
you cannot mix aggregated and non-aggregated values in a select statement – nkconnor Jan 17 '17 at 00:25

Gareth Daine · Answer 2 · 2017-01-13T23:02:26.267

0

OK, I believe this may work, as previous attempts didn't:

$sub = Reading::orderBy('created_at','desc');

return DB::table(DB::raw("({$sub->toSql()}) as sub"))
    ->whereIn('device_id', $ids)
    ->groupBy('device_id')
    ->get();

You basically run a 'sub query'. So, retrieve the results in the order you need first, then run the second query to group them.

edited Jan 13 '17 at 23:02

answered Jan 13 '17 at 21:27

Gareth Daine

4,016
5
40
67

be careful with this. depending on the storage engine and the indexes the order may be way off – nkconnor Jan 17 '17 at 00:34

score 0 · Answer 3 · edited May 23 '17 at 10:29

This is kinda a tricky question to think about. Once you re-frame the question in database lingo, something like "get max record for each id in MySQL query", it is quite easy to find a lot of SO answers.

Here's a highly rated example on SO: Retrieving the last record in each group

In your updated query, the subquery is actually recomputed for every row I think. We can do better by joining readings to itself (check above link as reference)

SELECT r1.*
FROM readings r1
  LEFT JOIN readings r2
    ON r2.device_id = r1.device_id
       AND r1.date_created < r2.date_created
WHERE r2.device_id IS NULL # there is no row with a date_created greater than r1's
;

Indexes on device_id and date_created are an easy first step for making this faster

also remember you can add another where clause, `AND r2.device_id IN ($ids)`, and if they are numeric, `r2.device_id > ($lowest_id) AND r2.device_id < ($highest_id)`. This is likely better than looping through the results in code. — nkconnor, Jan 16 '17 at 17:56

score 0 · Answer 4 · answered Jan 18 '17 at 21:21

I was finally able to get it sorted out. The following query returns exactly what i want in laravel.

$sql = Reading::select('created_at', 'column1', 'column2', 'column3', 'column4', 'column5',
'column6')->whereIn('device_id', ['0','0'])->toSql(); 

// 0 is just "empty" value, it doesn't matter here yet

$db = DB::connection()->getPdo();
$query = $db->prepare($sql);

// real ids im looking for

$query->execute(array('ffvr5g6h', 'ccvl5f4rty'));


$collection = collect($query);

// change this to take ever (n) record 

//$collection = $collection->every(100);
$collection = $collection->groupBy('device_id');

 foreach ($collection as $collect) {
                // process data
               $end = $collect->last();
               print_r($end);
            }

This query above is querying 100K rows in about half a second, then turning that into a collection for laravel, once in a collection i group by id, then grab the last record from that result.

Thanks to all those who tried to help :)

laravel 5.3 - order results via whereIn mysql

4 Answers4