Question 202323 in Laravel Question posted in
The official Laravel Documentation can be found here.

SQL (& Laravel): getting the latest row of each group with condition

I have this data in db:

id | user_id | status
1  | 7 | sent
2  | 7 | pending
3  | 8 | pending
4  | 8 | sent

how do I make a query so that I get this result:

id | user_id | status
4  | 8 | sent

this query is not working as expected:
MyTable::where('status', 'sent')->groupBy('user_id')->get();

result:

id | user_id | status
1  | 7 | sent
4  | 8 | sent

the latest status in db on user_id of 7 is pending and I don’t want to include it in the result.

please help

fyi, I’m using Laravel 9. thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    solved:

    $rawQuery = "(SELECT MAX(my_tables.id) AS max_id FROM my_tables GROUP BY my_tables.user_id) AS latest";

MyTable::->join(
 DB::raw($rawQuery),
 function ($join) {
  $join->on('my_tables.id', '=', 'my_tables.max_id');
 })
 ->where('status', 'sent')
 ->get();

  2. 0

    you should select only the last status where it is ‘sent’

    somethink like:

    $result = Users::whereIn('id', function ($query) {
    $query->selectRaw('MAX(id)')
        ->from('users')
        ->groupBy('user_id')
        ->havingRaw("MAX(CASE WHEN status = 'sent' THEN id END) = MAX(id)");
})
->where('status', 'sent')
->get();

    I did not got the change to test it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search