MySQL to Eloquent query

Question

I've been having some issues with a query. I'm trying to get the most recent record for each 'category' from a table, but I can't figure out how to write the query in Laravel.

I got the query working in MySQL, but no real luck in translating it.

The MySQL query looks like this:

SELECT *
FROM messages
WHERE id IN (
   SELECT MAX(id)
   FROM messages
   GROUP BY conversation_id
);

I was trying something like this in Laravel, but it doesn't seem to work:

return self::where(function($query){
        $query->select(max(['id']))
        ->from('messages')
        ->groupBy('conversation_id');
    })
    ->get();

Possible duplicate of: http://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in http://stackoverflow.com/questions/27064678/how-to-create-a-subquery-using-laravel-eloquent — pumbo, Mar 25 '17 at 16:47

score 0 · Accepted Answer · answered Mar 30 '17 at 20:58

(Posted on behalf of the OP).

Thanks to AlexM's comments I figured it out.

return self::whereIn('id', function($query){
        $query->select(max(['id']))
        ->from('messages')
        ->orderBy('created_at', 'desc')
        ->groupBy('conversation_id');
    })
    ->get();

Was my first solution but that didn't work quite well. It was selecting two records as intended, but not the last ones.

I've then come up with the idea to use selectRaw instead select, which solved my issue perfectly. The final query looks like this, for any interested:

return self::whereIn('id', function($query){
        $query->selectRaw('max(id)')
        ->from('messages')
        ->orderBy('created_at', 'desc')
        ->groupBy('conversation_id');
    })
    ->get();

MySQL to Eloquent query

1 Answers1