CODEWITHSUNDEEP
phplaravellaravel-query-builder
Jorge Ga
Jorge Ga

Reputation: 23

Usuing Join, Max + groupBy orderBy Query Builder Laravel

Im trying to translate this query from mysql

SELECT usuario_cia.idusuario_cia,usuario_cia.nombre,usuario_cia.fkcompania,
            mobil_gps.idmobil_gps,  mobil_gps.fkmobil,MAX(mobil_gps.fechahora), mobil_gps.bateria 
    FROM usuario_cia 
    INNER JOIN mobil_gps ON mobil_gps.fkusuario= usuario_cia.idusuario_cia
    WHERE usuario_cia.fkcompania=24  
    GROUP BY idusuario_cia 
    ORDER BY mobil_gps.fechahora DESC;

to a function in my Laravel model with Query Builder, I have a big problem with the max, rigth now I have this:

$datos=$query->join('mobil_gps','mobil_gps.fkusuario','usuario_cia.idusuario_cia')

    ->select( 'usuario_cia.idusuario_cia','usuario_cia.nombre','usuario_cia.fkcompania',
        \DB::raw('mobil_gps.idmobil_gps'),\DB::raw('mobil_gps.fechahora'), \DB::raw('mobil_gps.fkmobil'),\DB::raw('mobil_gps.bateria'))

    ->where('usuario_cia.fkcompania', $id_compania)
    //->max(\DB::raw('mobil_gps.fechahora'))
    ->groupBy('usuario_cia.idusuario_cia')
    ->orderBy(\DB::raw('mobil_gps.fechahora'), 'desc')

    ->get()->toArray();

but if I uncoment the max part everithing crash.

Could u give me any suggestion?

Upvotes: 2

Views: 233

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

You have to use MAX() in select():

->select(..., \DB::raw('MAX(mobil_gps.fechahora)'), ...)

This is also the only place in your query where you need DB::raw().

Upvotes: 1

Related Questions