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?

score 68 · Accepted Answer · edited Jan 23 '19 at 22:05

68

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

edited Jan 23 '19 at 22:05

Robert Pounder

1,490
1
14
29

answered Apr 15 '14 at 02:08

Tim Groeneveld

8,739
3
44
60

it is possible to make it chain `toArray()` ? I got an error if I append with ->toArray(). – Shiro Apr 15 '14 at 03:27
@Shiro `toArray()` is a model method of Eloquent, not of DB. You will need to use `whereRaw()` instead. I have updated my answer. – Tim Groeneveld Apr 15 '14 at 05:05
thanks for your answer, may I know why u put array(25) for it? – Shiro Apr 15 '14 at 05:49
Raw queries are not necessary here, one of the other answers are a better solution. – Erik Berkun-Drevnig Dec 02 '16 at 20:50
@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. – Tim Groeneveld Dec 04 '16 at 22:34

score 56 · Answer 2 · answered Apr 15 '14 at 01:40

56

Just like the docs say

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

answered Apr 15 '14 at 01:40

Ohgodwhy

49,779
11
80
110

1

I would like to get the whole record,not only the max id, and I do not want make it two queries. – Shiro Apr 15 '14 at 01:47
@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 – Ohgodwhy Apr 15 '14 at 02:17
Thanks for being simple ! – code-8 Aug 03 '16 at 15:53
I needed the next ID: protected function find_next_id_number() { return Module::max('id') + 1; } – dustbuster Feb 01 '19 at 20:15
Be careful when using ->max('id') with SoftDeletes ... For some reason it doesn't count "Trashed" items ... – Bart Mommens Jun 30 '22 at 21:06

Afraz Ahmad · Answer 3 · 2018-05-15T05:43:02.207

34

For Laravel ^5

Orders::max('id');

I used it is short and best;

edited May 15 '18 at 05:43

answered Apr 22 '17 at 18:26

Afraz Ahmad

5,193
28
38

Govind Samrow · Answer 4 · 2022-02-04T06:45:34.840

19

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

edited Feb 04 '22 at 06:45

answered Jun 16 '16 at 11:16

Govind Samrow

9,981
13
53
90

2

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')` – Edward Oct 29 '18 at 13:11
@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 Jul 25 '21 at 12:12
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. – tristanbailey Jul 25 '21 at 12:22

score 5 · Answer 5 · answered Feb 28 '16 at 10:24

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.

answered Feb 28 '16 at 10:24

tristanbailey

4,427
1
26
30

Davide Casiraghi · Answer 6 · 2022-01-15T04:31:28.307

1

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

edited Jan 15 '22 at 04:31

answered Jan 07 '22 at 13:24

Davide Casiraghi

15,591
9
34
56

Its nice code, but has to be inefficient right? As `max()` performs an extra select on the database in this case. – RonnyKnoxville Mar 10 '23 at 12:09

Laravel Query Builder where max id

6 Answers6

Linked