Ad

Return Only The Highest Equipment Maintenance Id

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 )
Ad

Answer

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.

Ad
source: stackoverflow.com
Ad