php - 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?

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