如何使用Eloquent和MySQL按最后一个ID排序分组结果？

Question

I tried this query but it only order id column, rest is not.

$chapters = Test::select(DB::raw('*, max(id) as id'))
        ->groupBy('id_equip')
        ->orderBy('id', 'asc')
        ->get();

Answer 1

In MySQL when using group by you can't rely on order by clause (it won't work as you expect, ie. it will not order the results in groups, but rather return random row from the group).

So in order to achieve what you want, you need a subquery or join:

// assuming tests table, group by id_equip, order by id
SELECT * FROM tests WHERE id = (
   SELECT MAX(id) FROM tests as t WHERE t.id_equip = tests.id_equip
) ORDER BY id

SELECT * FROM tests 
   JOIN (SELECT MAX(id) as id FROM tests ORDER BY id DESC) as sub
      ON sub.id = tests.id

This will get the highest id for each id_equip and return whole row for each of them.

---

Now, in eloquent I suggest first approach, if you want it to look more intuitive:

Test::where('id', function ($sub) {
   // subquery
   $sub->selectRaw('max(id)')
       ->from('tests as t')
       ->where('t.id_equip', DB::raw('tests.id_equip'));
// order by
})->orderBy('id', 'desc')->get();

but 2nd appreach is probably the way if you have big table to scan (in terms of performance):

Test::join( DB::raw(
    '(select max(id) as id from tests group by id_equip order by id desc) sub'
  ), 'sub.id', '=', 'posts.id')
  ->get(['tests.*']);

Here you need to set order by clause inside the raw join statement.

You could also build that join subquery with the builder if you like:

$sub = Test::selectRaw('max(id)')
        ->groupBy('id_equip')
        ->orderBy('id', 'desc')
        ->toSql();

Test::join( DB::raw(
    "({$sub}) as sub"
  ), 'sub.id', '=', 'posts.id')
  ->get(['tests.*']);