Laravel: How to get last N entries from DB
PhpDatabaseLaravelLaravel 4EloquentPhp Problem Overview
I have table of dogs in my DB and I want to retrieve N latest added dogs
.
Only way that I found is something like this:
Dogs:all()->where(time, <=, another_time);
Is there another way how to do it? For example something like this Dogs:latest(5);
Thank you very much for any help :)
Php Solutions
Solution 1 - Php
You may try something like this:
$dogs = Dogs::orderBy('id', 'desc')->take(5)->get();
Use orderBy
with Descending
order and take the first n
numbers of records.
Update (Since the latest
method has been added):
$dogs = Dogs::latest()->take(5)->get();
Solution 2 - Php
My solution for cleanliness is:
Dogs::latest()->take(5)->get();
It's the same as other answers, just with using built-in methods to handle common practices.
Solution 3 - Php
Dogs::orderBy('created_at','desc')->take(5)->get();
Solution 4 - Php
You may also try like this:
$recentPost = Article::orderBy('id', 'desc')->limit(5)->get();
It's working fine for me in Laravel 5.6
Solution 5 - Php
You can pass a negative integer n to take the last n elements.
Dogs::all()->take(-5)
This is good because you don't use orderBy which is bad when you have a big table.
Solution 6 - Php
I use it this way, as I find it cleaner:
$covidUpdate = COVIDUpdate::latest()->take(25)->get();
Solution 7 - Php
Ive come up with a solution that helps me achieve the same result using the array_slice()
method. In my code I did array_slice( PickupResults::where('playerID', $this->getPlayerID())->get()->toArray(), -5 );
with -5
I wanted the last 5 results of the query.
Solution 8 - Php
The Alpha's solution is very elegant, however sometimes you need to re-sort (ascending order) the results in the database using SQL (to avoid in-memory sorting at the collection level), and an SQL subquery is a good way to achieve this.
It would be nice if Laravel was smart enough to recognise we want to create a subquery if we use the following ideal code...
$dogs = Dogs::orderByDesc('id')->take(5)->orderBy('id')->get();
...but this gets compiled to a single SQL query with conflicting ORDER BY
clauses instead of the subquery that is required in this situation.
Creating a subquery in Laravel is unfortunately not simply as easy as the following pseudo-code that would be really nice to use...
$dogs = DB::subQuery(
Dogs::orderByDesc('id')->take(5)
)->orderBy('id');
...but the same result can be achieved using the following code:
$dogs = DB::table('id')->select('*')->fromSub(
Dogs::orderByDesc('id')->take(5)->toBase(),
'sq'
)->orderBy('id');
This generates the required SELECT * FROM (...) AS sq ...
sql subquery construct, and the code is reasonably clean in terms of readability.)
Take particular note of the use of the ->toBase()
function - which is required because fromSub()
doesn't like to work with Eloquent model Eloquent\Builder
instances, but seems to require a Query\Builder
instance). (See: https://github.com/laravel/framework/issues/35631)
I hope this helps someone else, since I just spent a couple of hours researching how to achieve this myself. (I had a complex SQL query builder expression that needed to be limited to the last few rows in certain situations).
Solution 9 - Php
Imagine a situation where you want to get the latest record of data from the request header that was just inserted into the database:
$noOfFilesUploaded = count( $request->pic );// e.g 4
$model = new Model;
$model->latest()->take($noOfFilesUploaded);
This way your take()
helper function gets the number of array data that was just sent via the request.
You can get only id
s like so:
$model->latest()->take($noOfFilesUploaded)->puck('id')
Solution 10 - Php
For getting last entry from DB
$variable= Model::orderBy('id', 'DESC')->limit(1)->get();
Solution 11 - Php
use DB;
$dogs = DB::select(DB::raw("SELECT * FROM (SELECT * FROM dogs ORDER BY id DESC LIMIT 10) Var1 ORDER BY id ASC"));