How do I generate the query below with Eloquent , since Group By of error in 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
To generate this query you have 2 ways to do it.
In case I'm going to assume that the name of your table is even positions, but what I'll try to explain is worth to any table you want to use from the database.
Using DB library or Laravel's Elonquent, both are excellent but each one 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, use the laravel's own artisan wizard, 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 app folder.
With the created model you enter it and define the table that will be used in the bank through the line of code:
protected $table = "positions"
If you do not have the created_at, updated_at, and deleted_at fields in your table of type timestamps (I'm assuming you're using MySQL as a database) you have to disable the timestamps of it (I'm implying that you'll go the laravel elonquent 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;
This step is ready, now let's use your enloquent.
Firstly you need to configure your model inside your controller if you do not want to keep hitting your head with references.
Then you will get and put the following one line below some term use
use App\Positions;
Ready, your control is already referenced with your model.
Now we are going to create your query within the method, there are 2 ways to do it, apparently there is no difference, but technically it has differences in relation to memory and etc, but it is not the focus of the answer so I leave this as a curiosity for your searches.For you to do the 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 form is as Virgilio Novic said above, but you can not forget from above after the declaration of the namespace of your controller put the use DB
Basically, it's not very difficult or anything, you did not say which version of laravel you're using, so I assume it's the latest one, I'd advise you to read the documentation very easily and simply understanding.
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();