Как вытащить родительский элемент верхнего уровня дерева категорий?

Question

Есть игрушечная БД SQLite с таблицами:

Products: id, name, number, price, category_id.

Orders: id, client_id, datetime, sum.

OrderProducts: order_id, product_id, product_count.

Categories: id, parent_id, name.

CLients: id, name, address.

Надо сделать представление (view) «Топ-5 самых покупаемых товаров за последний месяц» (по количеству штук в заказах). В отчете должны быть: Наименование товара, Категория 1-го уровня, Общее количество проданных штук.

Код написал, работает ок.

SELECT Products.Name, Count(*) * Product_count AS Result_count, Categories.Parent_id
FROM Orders
JOIN OrderProducts ON Orders.Id = OrderProducts.Order_id
JOIN Products ON OrderProducts.Product_id = Products.Id
JOIN Categories ON Products.Category_id = Categories.Id
WHERE Orders.Created_at > DATETIME('now', '-30 day')
GROUP BY Product_id
ORDER BY Result_count DESC LIMIT 5;

Как мне сюда прикрутить получение самой верхней родительской категории?

Умею делать получение дочерних элементов через рекурсивный запрос, но как в обратную сторону не понимаю.

upd

Добавил как выглядит таблица категорий:

INSERT INTO Categories(Id, Parent_id, Name, Number) VALUES 
(1, null, 'Бытовая_техника', null),
(2, 1, 'Для кухни', 1),
(3, 2, 'Холодильное оборудование', 1),
(4, 3, 'Аксeссуары к холодильникам', 1),
(5, 4, 'формы для льда и мороженного', 1),
(6, 4, 'полки, поддоны и корзины', 2),
(7, 4, 'фильтры для холодильника', 3),
(8, 1, 'Для дома', 2),
(9, 8, 'Глаженье', 1),
(10, 9, 'Утюги', 1),
(11, 9, 'Парогенераторы', 2),
(12, null, 'Смартфоны и фототехника', null),
(13, 12, 'Смартфоны и гаджеты', 1),
(14, 13, 'Смартфоны', 1),
(15, 12, 'Планшеты и электронные книги', 2),
(16, 15, 'Планшеты', 1)

Колонка number это место (номер?) потомка в его node. Не очень нужно.

Answer 1

WITH RECURSIVE owners (Id, Parent_Id, Name, Number) AS
(
    SELECT Id, Parent_Id, Name, Number
        FROM Categories c
        WHERE c.Id  = 9
    UNION ALL 
    SELECT c1.Id, c1.Parent_Id, c1.Name, c1.Number
        FROM (owners o JOIN Categories c1 ON (o.Parent_Id = c1.Id))
 ) SELECT * FROM owners;

Schema (MySQL v9)

CREATE TABLE Categories(
    Id int not NULL,
    Parent_Id int,
    Name varchar(50),
    Number int
);

INSERT INTO Categories(Id, Parent_id, Name, Number) VALUES 
(1, null, 'Бытовая_техника', null),
(2, 1, 'Для кухни', 1),
(3, 2, 'Холодильное оборудование', 1),
(4, 3, 'Аксeссуары к холодильникам', 1),
(5, 4, 'формы для льда и мороженного', 1),
(6, 4, 'полки, поддоны и корзины', 2),
(7, 4, 'фильтры для холодильника', 3),
(8, 1, 'Для дома', 2),
(9, 8, 'Глаженье', 1),
(10, 9, 'Утюги', 1),
(11, 9, 'Парогенераторы', 2),
(12, null, 'Смартфоны и фототехника', null),
(13, 12, 'Смартфоны и гаджеты', 1),
(14, 13, 'Смартфоны', 1),
(15, 12, 'Планшеты и электронные книги', 2),
(16, 15, 'Планшеты', 1)

Query #1

with recursive owners (Id, Parent_Id, Name, Number) as
(
select Id, Parent_Id, Name, Number
    from Categories c
    where c.Id  = 9
union ALL 
SELECT c1.Id, c1.Parent_Id, c1.Name, c1.Number
    FROM (owners o JOIN Categories c1 ON (o.Parent_Id = c1.Id))
)select * from owners;

Id	Parent_Id	Name	Number
9	8	Глаженье	1
8	1	Для дома	2
1		Бытовая_техника

направление рекурсии зависит от условия в JOIN и на какое поле вы нацеливаете начальное условие

БЛОГ НА HUSL

Как вытащить родительский элемент верхнего уровня дерева категорий?

Ответы (1 шт):