即使在服务器上使用ORDER BY子句而不是localhost，SQL查询也会给我随机顺序

Question

I have this query in Eloquent (Laravel)

$cats = App\Category::leftJoin('categories as b', 'categories.cat_parent', 'b.id')
        ->orderBy('b.id')
        ->get([
            'b.id as father_id',
            'categories.img as img',
            'b.descr as child_descr',
            'categories.id as child_id',
            'categories.descr as father_descr'
        ]);

which always gives me the same order when rendered in my view on localhost but when I go to the website, the order changes after each refresh.

What the * is going on ? Can anyone please help me to fix this ?

Both localhost and server use MySQL.

Table categories is defined as (id, descr, cat_parent, img, slug).

Answer 1

You are sorting by one key b.id. Given that this is called "father_id", I speculate that multiple rows have the same value for b_id.

Sorting in SQL is not stable. That means that the ordering of values with the same keys is not guaranteed -- and can even change from one call to the next. Why is ordering not stable? That is simple: SQL tables represent unordered sets. Hence, there is no "natural" ordering for the rows, apart from the explicitly specified keys.

I would suggest that you introduce a stable sort by including a second key. I don't know what this would be, but I might speculate that child_id would uniquely identify each row and hence make the sort stable.