laravel eloquent query group by last id

Question

Hi I'm trying to get the last register in DB for each equipment

I got like

id   | id_equip
-----+----------
   1 |    3
   2 |    3
   3 |    3
   4 |    2
   5 |    2

I want to query like the last id of each equip like:

  id   | id_equip
  -----+----------
  3    |    3
  5    |    2

I tried this:

$calibracao = $this->calibracao->orderBy('id', 'DESC')->groupBy('id_equip')->get();

thanks for the help!

I want to order by but I only get the first of each equip, and what I want is the last one — Lucas Campos, Oct 24 '14 at 19:13

damiani · Answer 1 · 2014-10-25T00:01:11.873

A simple way, without join, using max:

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

(Assuming, of course, that you can count on your id to be in date order.)

You can also do this with Eloquent, using a self-referencing one-to-one relationship. The query takes longer, but it's much more Laravel-like:

Define the relationship in your model:

class myModel extends Eloquent {
    public function latestEquipment()
    {
        return $this->hasOne('myModel', 'id_equip', 'id_equip')->latest();
    }
}

(Note that in this case, we're using latest() so that we don't rely on the order of ids to determine the most recent entry, but rather on the created_at date of each record. This is more accurate.)

To retrieve all the records, with their latest equipment entry:

$latest = myModel::with('latestEquipment')->groupBy('id_equip')->get();
// loop over the results
foreach ($latest as $l) {
    if ($l->equipment) {
        echo('ID: ' . $l->id . 'ID_EQUIP: ' . $l->id->id_equip . '<br>');
    }
}

score 0 · Answer 2 · answered Oct 24 '14 at 19:44

I'm not sure if there's an easier way and this is kind of convoluted because of the join, but it should give the result you want:

DB::table('equip as e')
    ->select('e.*')
    ->join(DB::raw("(SELECT id_equip, MAX(id) id FROM equip GROUP BY id_equip) as _e"), function ($join) {
        $join->on('e.id', '=', '_e.id')->on('e.id_equip', '=', '_e.id_equip');
    })
    ->orderBy('id', 'asc')
    ->get();

score 0 · Answer 3 · answered Oct 27 '14 at 13:36

public function latest($equipamentos)
    {
        foreach ($equipamentos as $equip) 
        {
            $latest[$equip->cod] = DB::table('tb_calibracao')
                                ->where('cod_equipamento', $equip->cod)
                                ->where('parecer', '1')
                                ->orderBy('cod', 'Desc')
                                ->first();
        }

        return $latest; 
    }

That worked, thx for the help!

laravel eloquent query group by last id

3 Answers3

Linked