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.
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.