Использую СУБД postgresql.
Есть иерархическая таблица с каталогами данных (в примере корнями каталогов являются Корень1, Корень2 у них поле parent и root
заполнено null)
Рабочий пример
https://www.db-fiddle.com/f/2hbRo3qUfhwACNHVBxy3A9/0
CREATE TABLE rec (
id INTEGER,
full_code VARCHAR (255),
code VARCHAR (255),
parent INTEGER,
root INTEGER,
is_node bool
) ;–DEFAULT CHARSET=utf8 ;
INSERT IGNORE INTO rec
(id, full_code, code, parent, root, is_node)
VALUES
(1, ‘Корень1’,‘Корень1’, null, null, true),
(2, ‘Корень1.2017-12-31’, ‘2017-12-31’, 1, 1, false),
(3, ‘Корень1.2016-12-31’, ‘2016-12-31’, 1, 1, false),
(4, ‘Корень1.2015-12-31’, ‘2015-12-31’, 1, 1, false),
(5, ‘Корень1.111’, ‘111’, 1, 1, null),
(6, ‘Корень1.111’, ‘111’, 4, 4, null),
(7, ‘Корень1.111’, ‘111’, 3, 3, null),
(8, ‘Корень1.111’, ‘111’, 2, 2, null),
(9, ‘Корень1.222’, ‘222’, 2, 2, null),
(10, ‘Корень1.333’, ‘333’, 3, 3, null),
(11, ‘Корень2’,‘Корень2’, null, null, true)
В каждом каталоге есть папки с датой, в этой папке лежат записи актуальные до указанной даты,
у таких папок поле is_node имеет значение false.
Нужно написать запрос, который по значению full_code и дате, вернет запись актуальную на указанную дату.
Если будет передан полный код корня каталога или полный код записи с папкой, то нужно вернуть
актуальную папку с датой.
Например:
- если full_code = ‘Корень1.111’ Дата = ‘2017-02-02’, то должно вернуться
(8, ‘Корень1.111’, ‘111’, 2, 2, null) - если full_code = ‘Корень1.111’ Дата = ‘2019-02-02’, то должно вернуться из корня запись
(5, ‘Корень1.111’, ‘111’, 1, 1, null) - если full_code = ‘Корень1.111’ Дата = ‘2012-02-02’, то должно ничего не вернут, так как
есть папка с датой 2015-12-31 в ней нет записи с кодом ‘Корень1.111’ - если full_code = ‘Корень1’ Дата = ‘2017-02-02’, то должно вернуться
(2, ‘Корень1.2017-12-31’, ‘2017-12-31’, 1, 1, false) - если full_code = ‘Корень1’ Дата = ‘2019-02-02’, то должно вернуться
(1, ‘Корень1’,‘Корень1’, null, null, true) - если full_code = ‘Корень1.2017-12-31’ Дата = ‘2019-02-02’, то должно вернуться
(1, ‘Корень1’,‘Корень1’, null, null, true)
Написал запрос, логика такая узнаем значение id корня каталога, затем строим
список из корня и папок с датами, сравниваем с переданной датой, берем актуальную запись с датой,
проверяем есть ли там искомый полный код.
Но запрос получился слишком сложный:
WITH ROOT AS(
select (
case WHEN r1.root is NULL THEN r1.id
WHEN r1.is_node is False THEN r1.root
else (select case WHEN r2.root is NULL THEN r2.id else r2.root end from catalog r2 where r2.id = r1.root LIMIT 1) end ) as root
from catalog r1
where r1.full_code = ‘Корень1.111’
limit 1),
T AS(
select id
from catalog
where (parent = (select * from root) and is_node is false
AND code >= ‘2017-02-02’::text) OR id= (select * from root) ORDER BY parent NULLS LAST, code
LIMIT 1 ),
IDS AS(
select ( select * from T ) as id
from catalog
where (parent is null or is_node is false) and full_code = ‘Корень1.111’
union
select id
from catalog
where root = (select * from t) and full_code = ‘Корень1.111’ and (parent is not null and is_node is not false)
limit 1)
SELECT *
FROM catalog
where id in (select * from ids)
Вопросы такие:
- Как упростить запрос?
- Главный вопрос, как написать запрос, если передан массив полных кодов, и нужно получить массив актуальных записей.
Даже нет идей, как решить этот вопрос???