Return only the highest equipment maintenance id

Question

I want to get the highest maintence_id for equipament to show to a user. I can do this in SQL, but not in Eloquent or QueryBuilder.

Eloquent returns all the maintences for my view:

$equipament = Maintence::SELECT('maintences.*', 'equipaments.patrimony as patrimony')
            ->LEFTJOIN('equipaments', 'maintences.equipament_id', '=', 'equipaments.id' )   
            ->get();

SQL returning the highest maintence_id for equipament:

select * 
from  equipaments
left join maintences on maintences.equipament_id = equipaments.id 
and maintences.id = (select max(id) from maintences as main 
where 
main.equipament_id = equipaments.id )

Thales Bitencourt · Answer 1 · 2019-07-25T00:38:49.107

I don't recommend you use inner query inside JOIN clausule or either in SELECT one... It will run it for "each" rows. It's better you declare on Model Class a relation who will get the last one. See code above:

class Equipament extends Model {

 //Declaration of relation with Maintence
 public function maintences() {
  return $this->hasMany(Maintence::class);
 }

 //Ddeclaration of last Maintence relation filter
 public function lastMaintence() {
  return $this->maintences()->orderBy('id', 'desc')->limit(1);
 }

}

Then you can use like this:

$equipament = Equipament::with('lastMaintence')->find(1);
$lastMaintence = equipament->lastMaintence()->first();

I'll try it! Thanks for your help. – Augusto Von Grafen Jul 25 '19 at 12:59 — Augusto Von Grafen, Jul 25 '19 at 12:59

score 0 · Answer 2 · answered Jul 24 '19 at 20:06

0

Try it:

Maintence::join( DB::raw(
    '(select max(id) as id,equipament_id as equipament_id  from maintences group by equipament_id order by id desc) sub'
  ), 'sub.equipament_id', '=', 'equipaments.id')
  ->get(['maintences.*']);

answered Jul 24 '19 at 20:06

guiCunha

343
1
4

Return this error: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'equipaments.id' in 'on clause' (SQL: select `maintences`.* from `maintences` inner join (select max(id) as id,equipament_id as equipament_id from maintences group by equipament_id order by id desc) sub on `sub`.`equipament_id` = `equipaments`.`id`) – Augusto Von Grafen Jul 24 '19 at 20:15

score 0 · Answer 3 · answered Jul 24 '19 at 20:25

0

Have a the same problem here. Always search for problem using more simple term as possible. It's works! :)

About the problem. Why max ID and don't last version of equipament, for example? Check what you really want because sequence ID, for example, necessarily isn't the more updated.

answered Jul 24 '19 at 20:25

Rafael Pizao

742
8
21

1

Thanks for your help Rafael. I'll check this the next time! – Augusto Von Grafen Jul 24 '19 at 20:50

Zeshan · Accepted Answer · 2019-07-24T20:33:59.093

I think this is what you're looking after:

Equipment::select('equipments.id', 'equipments.name', 'maintenances.id as maintenance_id')
        ->leftJoin( \DB::raw(
            '(select max(id) as id,equipment_id as equipment_id  from maintenances group by equipment_id order by id desc) maintenances'
          ), 'maintenances.equipment_id', '=', 'equipments.id')
          ->get();

I assume your table names are equipments and maintenances, if not, please adjust the table names accordingly within the provided query.

Return only the highest equipment maintenance id

4 Answers4