Блог

У меня есть таблица продуктов, которая содержит все мои продукты. Таблица этих продуктов постоянно заполняется новыми продуктами. Тем не менее, я хочу иметь возможность «задерживать» / «привязывать» определенные продукты к месту в возвращаемой коллекции запросов.

Означает, что я хочу установить что-то вроде rank_index , которое содержит номер, который должен иметь продукт в возвращаемой коллекции запросов.

Пример:

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

Давайте предположим, что порядок по умолчанию будет id . Но поскольку rank_index для продукта установлено значение id 4, я хотел бы получить коллекцию со следующим порядком возвращаемых идентификаторов : 1, 4, 3, 5, 2 .

Возможно ли это как-то сделать? rank_index Столбец был просто моей идеей. Я имею в виду.. Я также мог бы сделать это на php стороне и выполнить обычный запрос, который включает только products без an rank_index и тот, который содержит только products с an index_rank , и упорядочить их вручную на php стороне.

Однако, поскольку это требует много времени и вычислительной мощности, я ищу решение, которое выполняется базой данных… Есть идеи?

Кстати: я использую Laravel 8, если это имеет какое-либо значение.

С уважением

Это очень сложная проблема. Если вы попробуете другой подход, устанавливающий последовательные значения — например, 2 и 3 — вы увидите, что они не работают.

Могут быть более простые способы решения этой проблемы. Но вот подход грубой силы.

1. Он создает производную таблицу путем перечисления строк в исходной таблице.
2. Он добавляет в эту таблицу (используя a left join ) все значения с принудительным ранжированием.
3. Он присоединяется к остальным значениям путем перечисления пустых слотов как в table1 производной таблице, так и в ней.

Итак:

 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;
  

Используйте rank_index if это не null в качестве упорядочения, id в противном случае:

Поскольку вы хотите rank_index , чтобы он был впереди an id , -0.5 производится корректировка:

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

Вы можете использовать предложение и иметь правильное число, чтобы получить правильный порядок, поэтому IF

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

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

SELECT *
FROM table1
ORDER BY IF(rank_index IS NULL, id, rank_index   .01)
  

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

db<> скрипка здесь