laravel雄辩的查询分组按最后一个id [英] laravel eloquent query group by last id

问题描述

我正在尝试获取DB中每个设备的最后一个寄存器

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

我喜欢

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

我想像每个设备的最后一个ID一样查询:

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

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

我尝试过:

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

感谢您的帮助！

推荐答案

一种简单的方法，使用max无需join:

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

(当然，假设您可以指望id按照日期顺序排列.)

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

您还可以使用自我引用一对一关系对Eloquent 进行此操作.查询需要更长的时间，但更像Laravel:

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:

定义模型中的关系:

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

(请注意，在这种情况下，我们使用的是latest()，因此我们不必依赖id的顺序来确定最近的条目，而是依靠每条记录的created_at日期.这样更准确.)

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

这篇关于laravel雄辩的查询分组按最后一个id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！