[SOLVED] Yii2 Active Record Order By Primary Key ID not working

Yii2 Active Record Order By Primary Key ID not working

I have a pretty basic Yii2 ActiveRecord query.

$paperList = Paper::find()->orderBy(['id' => SORT_DESC])->all();

id is my primary key column which is of type int and AUTO_INCREMENT. When running the simple select * from paper order by id desc directly on MySQL it works perfectly and I get a list such as: 18,17,16

But when running the Yii2 code above, it keeps the order as 16,17,18

On a larger table it also seems to be sorting weird. It will return: 100, 101, 102, 20, 21

But should be returning: 102, 101, 100, 21, 20

If I use Query Builder with standard SQL in my code, then it's not an ActiveRecord anymore.

Solution

Your code looks correct.

AFAIK possible issues:

Conflicting logic or scopes, if you have defined custom scopes or filters in your model, they might override the orderBy clause.
Query caching - if query caching is enabled, the results might be coming from the cache, not reflecting the correct order.

Try this approach - if you're using a GridView or similar data presentation, use an ActiveDataProvider to manage the query:

use yii\data\ActiveDataProvider;

$dataProvider = new ActiveDataProvider([
    'query' => Paper::find()->orderBy(['id' => SORT_DESC]),
]);

If it doesn't help try to check the debug toolbar to confirm that the generated SQL query includes the ORDER BY clause.