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?

Toomey

Answer 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.).

Cerveny

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

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

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

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

@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. – Cerveny

Answer 2

Just like the docs say

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

Eau

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

@Toomey 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 – Eau

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

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

Answer 3

For Laravel ^5

Orders::max('id');

I used it is short and best;

Printmaking

Answer 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');
 

Cortes

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') – Enalda

@Enalda 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. – Alina

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. – Alina

Answer 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.

Alina

Answer 6

You can get the latest record added to the Orders table you can use an eloquent method to retrieve the max aggregate:

$lastOrderId = Order::max('id');

To retrieve a single row by the id column value, use the find method:

$order = Order::find(3);

So combining them, to get the last model added to your table you can use this:

$lastOrder = Order::find(Order::max('id'));

Radiation

Its nice code, but has to be inefficient right? As max() performs an extra select on the database in this case. – Enneahedron

Answer 7

Two ways

Orders::max('id');

with softdelete/trashed

Orders::withTrashed()->max('id');

Hamulus