Consulta Laravel Eloquent

Question

How to query below with Eloquent, in view of the fact that the Group By of error in the Eloquent.

SELECT
  otimibus_gps.positions.id,
  otimibus_gps.positions.deviceid,
  otimibus_gps.positions.devicetime,
  otimibus_gps.positions.latitude,
  otimibus_gps.positions.longitude
FROM
  otimibus_gps.positions
WHERE deviceid in ('1','2')
GROUP BY deviceid
ORDER BY devicetime DESC

Answer 1

To generate this query you have 2 ways to do it.

In case I will assume that the name of your table is positions even, but what I will try to explain to you is valid for any table you want to use from the database.

Using the DB library or Elonquent from Laravel, both are excellent but each works a little different, but the purpose is the same.

If you want to use Elonquent you need to create a model.

To create models, I orient using the Artisan wizard of the proper Laravel, you open a command prompt and go to the folder of your project and type:

php artisan make:model Positions

The model that will be created stays in your folder app.

With the model created you enter it and define the table that will be used in the database through the code line:

protected $table = "positions"

If you don’t own the fields created_at, updated_at and deleted_at in your type table timestamps (I am considering that you are using Mysql as a database) you have to disable the timestamps from it (I am implying that you will elonquent the Adjustable for inserts, edits and so on). And to do this even within your model you need to enter the following line of code:

public $timestamps = false;

That step is already ready, now let’s use your enloquent.

First you need to configure your model inside your controller in case you don’t want to keep bumping heads with references.

Then you will pick up and put the following one line below some term use

use App\Positions;

Ready, your control is already referenced with your model.

Now let’s create your query within the method, there are 2 ways to do, apparently there is no difference, but technically has differences in relation to memory and etc, but it is not the focus of the answer so I leave this as curiosity for your searches.

For you to query would look like this 1:

$positions = new Positions();
$positions = $positions->select('id', 'deviceid', 'devicetime', 'latitude', 'longitude')
    ->whereIn('deviceid', [1, 2])
    ->groupBy('id', 'deviceid', 'devicetime', 'latitude', 'longitude')
    ->orderBy('devicetime', DESC)
    ->get();

And the second way is how Virgilio Novic said above, but you can’t forget above after your controller’s namespace declaration use DB

Basically it is like this, it is not very difficult or anything, you did not say which version of the Standard you are using, so I assume it is the latest, I advise gives a good read in the documentation that it is very easy and simple to understand.

https://laravel.com/docs/5.5

Answer 2

You can try to use it as follows:

Model::select('id', 'deviceid', 'devicetime', 'latitude', 'longitude')
    ->whereIn('deviceid', [1, 2])
    ->groupBy('id', 'deviceid', 'devicetime', 'latitude', 'longitude')
    ->orderBy('devicetime', DESC)
    ->get();