#sql #postgresql #postgresql-10
#sql #postgresql #postgresql-10
Вопрос:
У меня есть 2 таблицы:
Категории
id | name | | slug | path | parent_id | depth
1 name1 slug1 {1} null 0
2 name2 slug2 {1,2} 1 1
3 name3 slug3 {1,2,3} 2 2
5 nam5 slug5 {5} null 0
......
9 nam4 slug9 {5,9} 5 1
где path является int[]array типом и работает как хлебная крошка
Товары
id | name
1 name1
Между элементом и категорией существует отношение M2M
item_categories
id | item_id | category_id
1 1 | 3
2 1 9
В приведенном выше примере элемент находится в 3 категориях:
Я использую следующий SQL:
SELECT c1.id, c1.name, c1.slug, c1.parent_id FROM categories AS c1
WHERE ARRAY[c1.id] <@ (SELECT c2.path FROM categories AS c2 WHERE c2.id=
(SELECT category_id FROM item_categories WHERE item_id=8 LIMIT 1)) order by depth
чтобы извлечь базу данных по пути, и это работает.
Но я хочу получить все хлебные крошки (а не только один). Удаление LIMIT 1 и изменение = to in У меня будет массив массивов, а не просто массив, и вызовет ошибку:
более одной строки, возвращаемой подзапросом, используемым в качестве выражения
что нормально.
Пример того, что я хочу — Элемент в:
cat1 - > cat2 - >cat3
ca5 -> cat9
и из базы данных (чтобы я мог перебирать их):
[ [{'name':cat1, 'slug':slug1}, {'name':cat2, 'slug':slug2}, {'name':cat3, 'slug':slug3}], [{'name':cat5, 'slug':slug5}, {'name':cat9, 'slug':slug9}]]
dbfiddle: https://dbfiddle.uk/?rdbms=postgres_10amp;fiddle=f756cfe568d38425dfe25cfec60b1b3f
Итак, вместо получения одной хлебной крошки, как я могу получить массив из хлебных крошек в качестве результата?
Комментарии:
1. (1) Пожалуйста, покажите результаты, которые вы хотите. (2) Можете ли вы настроить db<>скрипку или что-то в этом роде?
2. 1) Я обновляю вопрос с помощью примера
3. ваш оператор sql ссылается на вызываемую таблицу
categories_category, которая больше нигде не упоминается, это ошибка? Минимальный образец данных был бы полезен, вот начало , вы можете заполнить это?4. @JackDouglas спасибо, это была ошибка, которую я обновил в dbfiddle; в моем заказе запроса по глубине у меня есть путь от верхней категории до последнего дочернего элемента. Проблема возникает, когда мне нужно передать несколько путей; Поэтому я подумал использовать массив array с элементами json для их группировки;
5. Если у вас есть обновленный db<>fiddle, вам необходимо поделиться ссылкой с нами, каждая итерация скрипки имеет уникальную ссылку.
Ответ №1:
используя json_build_object unnest и упорядочивая json_agg :
select
c.id,
json_agg(
json_build_object('name',c2.name,'slug',c2.slug)
order by p.depth
)
from categories as c
inner join lateral unnest(c.path) with ordinality as p(id, depth) on true
inner join categories as c2 on
c2.id = p.id
where
exists (
select *
from item_categories as tt
where
tt.item_id = 1 and
tt.category_id = c.id
)
group by
c.id
Или вы можете использовать depth столбец из вашей таблицы, если хотите:
select
c.id,
json_agg(
json_build_object('name',c2.name,'slug',c2.slug)
order by c2.depth
)
from categories as c
inner join categories as c2 on
c2.id = any(c.path)
where
exists (
select *
from item_categories as tt
where
tt.item_id = 1 and
tt.category_id = c.id
)
group by
c.id
Что мне не нравится, так это то json_build_object , что вы должны называть свои столбцы явно, выполняя двойную работу, поэтому я попытался использовать to_json вместо этого. Это работает, но, честно говоря, я не очень знаком с этим синтаксисом, когда псевдоним таблицы передается функции в качестве аргумента (см. Using Composite Types in Queries ) и не может заставить его работать без lateral join:
select
c.id,
json_agg(to_json(d) order by c2.depth)
from categories as c
inner join categories as c2 on
c2.id = any(c.path)
cross join lateral (select c.name, c.slug) as d
where
exists (
select *
from item_categories as tt
where
tt.item_id = 1 and
tt.category_id = c.id
)
group by
c.id