What is the dafault ordering in Laravel Eloquent Model::all() function?

Question

In the Laravel Eloquent Model::all() function, what is the default ordering for the returned query? I ask this because I'm pretty sure it's in ascending order by primary key which defaults do 'id' when you make the model through

php artisan make:model Model -m

However, when I call it like this:

return $users = User::all();

I get the following results in the browser:

The results seem to be in no particular order by any of the attributes. I am fully aware I can order them by id by doing

return $users = User::orderBy('id', 'asc')->get();

But just a few days ago they were being ordered automatically. What gives?

Answer 1

The default sort order for laravel is simply nothing. It does not apply a default "ORDER BY" clause, which means that it follows PostgreSQL rules for unordered results. From some similar answers here and here:

• Do not depend on order when ORDER BY is missing.

• Always specify ORDER BY if you want a particular order -- in some situations the engine can eliminate the ORDER BY because of how it does some other step.

• GROUP BY forces ORDER BY. (This is a violation of the standard. It can be avoided by using ORDER BY NULL.)

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

Answer 2

With a great agreement with FrankerZ's answer, I would like to add, whenever you wonder what query laravel is structuring under the ORM, DB listener will be quickest option to use :

<?php

\DB::listen(function($sql) {
    dump($sql);
});

W.r.t. this question it would give select * from users when you do User::all().

Note: Check listen function as per the laravel version you are using, the parameter count are different in older and newer versions.

Reference : Documentation Link