How to make automatic sorting of goods according to the status in the warehouse?

Question

PHP

Hello. All products in the store have a quantity of 0 and a status of out of stock (for example: "In stock", "On order", "Out of stock"). In the category, the default sorting of products is made. I assign sort_id (sort order) to each product manually. I would like sort_id to be set automatically, depending on the status. All products with the status "In stock" - sort_order 1, "On order" - sort_order 2, "Out of stock" - sort_order 3. Tell me, how can this be done?
I searched in Google, I found such crutches, I don’t know if this is a working option.
in catalog\model\catalog\product.php

if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY p.stock_status_id DESC, LCASE(" . $data['sort'] . ")";
            } elseif ($data['sort'] == 'p.price') {
                $sql .= " ORDER BY p.stock_status_id DESC, (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
            } else {
                $sql .= " ORDER BY p.stock_status_id DESC, " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY  p.stock_status_id DESC, p.sort_order";

Answer 1

Something like this :)

SELECT *,
  if(`status` = 'В наличии',3,0) AS mystatus3,
  if(`status` = 'На заказ',2,0) AS mystatus2,
  if(`status` = 'Нет наличии',1,0) AS mystatus1
   FROM orders
ORDER BY mystatus3|mystatus2|mystatus1 DESC

Answer 2

SELECT
        CASE WHEN status="В наличии" THEN 1
            WHEN status="На заказ" THEN 2 
            WHEN status="Нет в наличии" THEN 3
            ELSE null END sort_id,
            *
    FROM table

In a good way (that is, to make it work faster), you need to normalize all this. Make a status table and refer to their id in the main table, and put sort_id in the new table.

Answer 3

$sql .= "ORDER BY p.stock_status_id DESC, p.sort_order";

All OK. Will be sorted first by stock_status_id, then by sort_order if one is given.
Make sure stock_status_id matches the order you want.