Consultations in Laravel

Asked

Viewed 242 times

1

I’m trying to run a query on Laravel, in the terminal works perfectly, but when I insert in the Laravel, the same returns me empty, example:

Consultation:

   SELECT 
    MAX(positions.id),
    devices.name,
    positions.deviceid,
    positions.servertime,
    positions.devicetime,
    positions.fixtime,
    positions.latitude,
    positions.longitude
FROM positions
INNER JOIN devices 
    ON positions.deviceid = devices.id
GROUP BY positions.deviceid

in the Laravel:

$ultimasPosicoes = PositionAll::select('SELECT 
            MAX(positions.id),
            devices.name,
            positions.deviceid,
            positions.servertime,
            positions.devicetime,
            positions.fixtime,
            positions.latitude,
            positions.longitude
            FROM
            positions
            INNER JOIN devices ON positions.deviceid = devices.id
            GROUP BY
            positions.deviceid');
mysql laravel-5 laravel-eloquent

  • just a hint, put your model name in the singular, and leave a name that indicates your role. Ex: Position.php

2 answers

2


You can use the DB::table() to make the query indicating the fields you need, would be more or less this way:

$positions = DB::table('positions')
->selectRaw('MAX(positions.id),
    devices.name,
    positions.deviceid,
    positions.servertime,
    positions.devicetime,
    positions.fixtime,
    positions.latitude,
    positions.longitude')
->join('devices', 'positions.deviceid', '=', 'devices.id')->groupBy('deviceid')->get();

Another way that was commented by Leandro RR is to use the Eloquent pure, in case instead of using the Facade DB we would use only the Model, staying that way:

$positions = Position::selectRaw('MAX(positions.id),
        devices.name,
        positions.deviceid,
        positions.servertime,
        positions.devicetime,
        positions.fixtime,
        positions.latitude,
        positions.longitude')
    ->join('devices', 'positions.deviceid', '=', 'devices.id')
    ->groupBy('positions.deviceid')
    ->get();

Reference: Database: Query Builder

  • My Buddy already would like to thank for the attention, but returned me an error Class 'App Http Controllers Api Tracking Position DB' not found, can help me?

  • 1

    you probably just copied the code and didn’t import the DB class

  • use Illuminate Support Facades DB;

  • remembering that if you are using eloquent Orm can use the selectRaw() to put expressions in pure dql.

  • You’re right @Leandrorr edited the answer

  • I think even the DB facade need not use, only use the eloquent pure even, calling the model Position::selectRaw() and there in the model it declares a variable protected calling for $table = 'positions';

  • Yes yes, that way it works too, it would just exchange DB::table('positions') for Position::selectRaw ...

Show 2 more comments

1

Thanks in advance for the help of all, we managed to solve in the following way:

$ultimasPosicoes =  PositionAll::selectRaw('MAX(positions.id),
                                        devices.name,
                                        positions.deviceid,
                                        positions.servertime,
                                        positions.devicetime,
                                        positions.fixtime,
                                        positions.latitude,
                                        positions.longitude')
            ->join('devices', 'positions.deviceid', '=', 'devices.id')
            ->groupBy('deviceid')
            ->get();

Browser other questions tagged mysql laravel-5 laravel-eloquent

You are not signed in. Login or sign up in order to post.