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

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      5
3      Baby car
4      Green carpet         2
5      Toy        

Lets assume the default order would be the id. But because the rank_index is set for the product with the id 4 I would like to get the collection with the following order of ids returned: 1, 4, 3, 5, 2.

Is this somehow possible to do? 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.

However, because this takes a lot of time and processing power I am looking for a solution which is done by the database… Any ideas?

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

Kind regards

Answer

This is a very tricky problem. If you try the other approach setting consecutive values — like 2 and 3 — you will see that they do not work.

There may be simpler ways to solve this. But, here is a brute force approach.

1. It constructs a derived table by enumerating the rows in the original table.
2. It adds into this table (using a left join) all the force-ranked values.
3. It joins in the rest of the values by enumerating the empty slots both in table1 and in the derived table.

So:

with recursive n as (
      select row_number() over (order by id) as n
      from table1 t1
     ),
     nid as (
      select n.n, t1.id
      from n left join
           table1 t1
           on t1.rank_index = n.n
     ),
     nids as (
      select n.n, coalesce(n.id, t1.id) as id
      from (select nid.*, sum(nid.id is null) over (order by nid.n) as seqnum
            from nid 
           ) n left join
           (select t1.*, row_number() over (order by id) as seqnum
            from table1 t1
            where rank_index is null
           ) t1
           on n.seqnum = t1.seqnum
     )
select t1.*
from nids join
     table1 t1
     on t1.id = nids.id
order by nids.n;