Mysql: Query which orders by default and rank (pin/hold up entries?)

Question

I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to "hold up"/"pin" certain products to a place in the returned query collection.

Means, I want to set something like rank_index which contains the number the product should have in the returned query collection.

Example:

id     title                rank_index
1      An awesome product
2      Another product      3
3      Baby car
4      Green carpet         1
5      Toy        

Lets assume the default order would be the id, descending. But descending? Because as I pointed out the products table gets permanently filled with new products and I always want to show the newest product on top. However, when e.g. an product has a rank_index of 2 the database always should return this entry as the second no matter how money new products are written to the database!

The result of the table above would be:

id     title                rank_index
4      Green carpet         1
5      Toy
2      Another product      3
3      Baby car
1      An awesome product

Is this somehow possible to do (without recursion)? The rank_index column was just an idea of mine. I mean.. I also could do this on the php side and do a normal query which does only include the products without an rank_index and one which only contains products with an index_rank and order them manually on the php side. Do you would recommend this? Would that be faster?

Furthermore, late on I want to add a perfomance_index which is not an +1 incrementing index but rather an index which show the performance of an product. This should be sorted descending as well if that makes any difference to this problem.

This here is doing a good job but only for ascending tables, I want a descending table with products being added permanently!

SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5)

Btw: I am using Laravel 8 if this makes any difference.

Kind regards

Answer 1

You can add to the ORDER BY a IF ot CASE, depending on the complexity or the new order.

In you case you ned to add the max(id) into the mix

CREATE TABLE table1 (
  `id` INTEGER,
  `title` VARCHAR(18),
  `rank_index` VARCHAR(4)
);

INSERT INTO table1
  (`id`, `title`, `rank_index`)
VALUES
  ('1', 'An awesome product', NULL),
  ('2', 'Another product', '3'),
  ('3', 'Baby car', NULL),
  ('4', 'Green carpet', '1'),
  ('5', 'Toy', NULL);

    SELECT t1.id, t1.`title`,t1.`rank_index`
    FROM table1  t1 CROSS JOIN ( SELECT MAX(id)  maxid FROM table1) t2 
    ORDER BY IF(rank_index IS NULL, maxid - id + 1 , rank_index - 0.5) ASC

id | title              | rank_index
-: | :----------------- | :---------
 4 | Green carpet       | 1         
 5 | Toy                | null      
 2 | Another product    | 3         
 3 | Baby car           | null      
 1 | An awesome product | null      

db<>fiddle here

Answer 2

I think you just have to add a descending clause to your order by, something like this:

SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5) DESC

The -0.5 I'm assuming is your own logic but certainly isn't a necessity to order by descending.

Also sorting is probably faster in the database then on the PHP side but either one probably has negligible differences.