Laravel Query Builder where max id

Question

How do I accomplish this in Laravel 4.1 Query Builder?

select * from orders where id = (select max(`id`) from orders)

I tried this, working but can't get the eloquent feature.

DB::select(DB::raw('select * from orders where id = (select max(`id`) from orders)'));

Any idea to make it better?

Solution 1

You should be able to perform a select on the orders table, using a raw WHERE to find the max(id) in a subquery, like this:

 \DB::table('orders')->where('id', \DB::raw("(select max(`id`) from orders)"))->get();

If you want to use Eloquent (for example, so you can convert your response to an object) you will want to use whereRaw, because some functions such as toJSON or toArray will not work without using Eloquent models.

 $order = Order::whereRaw('id = (select max(`id`) from orders)')->get();

That, of course, requires that you have a model that extends Eloquent.

 class Order extends Eloquent {}

As mentioned in the comments, you don't need to use whereRaw, you can do the entire query using the query builder without raw SQL.

 // Using the Query Builder
 \DB::table('orders')->find(\DB::table('orders')->max('id'));

 // Using Eloquent
 $order = Order::find(\DB::table('orders')->max('id'));

(Note that if the id field is not unique, you will only get one row back - this is because find() will only return the first result from the SQL server.).

Solution 2

Just like the docs say

DB::table('orders')->max('id');

Solution 3

For Laravel ^5

Orders::max('id');

I used it is short and best;

Solution 4

No need to use sub query, just Try this,Its working fine:

  DB::table('orders')->orderBy('id', 'desc')->pluck('id');

Laravel 5+:

  DB::table('orders')->orderBy('id', 'desc')->value('id');
 

Solution 5

For objects you can nest the queries:

DB::table('orders')->find(DB::table('orders')->max('id'));

So the inside query looks up the max id in the table and then passes that to the find, which gets you back the object.

View more solutions

Comments

[Shiro]

I would like to get the whole record,not only the max id, and I do not want make it two queries.

[Ohgodwhy]

@Shiro Sorry, you will have to perform a query chain for this, as laravel's fluent query builder will not allow this to be done as a single string, good luck

[Shiro]

it is possible to make it chain toArray() ? I got an error if I append with ->toArray().

[Tim Groeneveld]

@Shiro toArray() is a model method of Eloquent, not of DB. You will need to use whereRaw() instead. I have updated my answer.

[Shiro]

thanks for your answer, may I know why u put array(25) for it?

[code-8]

Thanks for being simple !

[Erik Berkun-Drevnig]

Raw queries are not necessary here, one of the other answers are a better solution.

[Tim Groeneveld]

@ErikBerkun-Drevnig, I have edited my post. I did write this answer so it could be used for any field. Also note that the original answer did not limit the query from returning more then one row. This is (IMHO) an important thing to note. For example, if I wanted to use this query to find user profiles with max(age) and min(age). I would not want to limit my results to one row.

[Edward]

This is a terrible solution. It forces MySQL to look at all your rows, filter them, sort them, and then return the result. Don't do this. Instead opt for something along the lines of Orders::max('id')

[dustbuster]

I needed the next ID: protected function find_next_id_number() { return Module::max('id') + 1; }

[tristanbailey]

@Edward why would this be terrible? If the table had an index on it (which as an 'id' most likely does) then the DB would handle that fine, as simplest order by.

[tristanbailey]

Ran a test and Laravel optimises it to select * from `orders` order by `id` desc limit 1 so not pulling all results as limit is set. If there was no key it would need to do more work.

[Bart Mommens]

Be careful when using ->max('id') with SoftDeletes ... For some reason it doesn't count "Trashed" items ...