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: stackoverflow.com/questions/16815551/… stackoverflow.com/questions/27064678/… — pumbo
– pumbo, Commented Mar 25, 2017 at 16:47

halfer · Accepted Answer · 2017-03-30 20:58:02Z

0

(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();

Collectives™ on Stack Overflow

MySQL to Eloquent query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related