Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Nested Sets выборка  [new]
xew
Member

Откуда:
Сообщений: 6
Здравствуйте, имеются 3ри таблицы связанные между собой многие к многим.

Таблица categories с полями:
id, name, link, parent_id, lft, rgt, depth

Таблица products с полями:
id, name, description, parameters, image, price

Таблица category_product с полями:
product_id, category_id

Задача вывести ВСЕ категории где есть товар со ВСЕМИ их родителями.
Реализовал такой код
Код:
SELECT * FROM categories INNER JOIN category_product ON category_product.category_id = categories.id GROUP BY categories.id

Который выводит категории данных товаров, но теперь мне нужно тут же получить и всех их родителей.
Пытался сделать как то так:
Код:
SELECT * FROM categories as cat1 INNER JOIN category_product ON category_product.category_id = cat1.id GROUP BY cat1.id INNER JOIN categories as cat2 ON cat2.lft <= cat1.lft AND cat2.rgt >= cat1.rgt ORDER BY cat2.lft

Но как бы не переделывал запрос постоянно проблемы с синтаксисом помогите его поправить.
Толи тут нужен вложенный запрос то ли я с join как то не так работаю :(
28 янв 16, 04:44    [18738539]     Ответить | Цитировать Сообщить модератору
 Re: Nested Sets выборка  [new]
xew
Member

Откуда:
Сообщений: 6
Сделал так, но теперь возникла проблема что данные продолжились в бок а не дополнились. Нужно использовать UNION только как в нем передать значения cat1.lft и cat1.rgt ?

Код:
SELECT * FROM categories as cat1
INNER JOIN category_product ON category_product.category_id = cat1.id
INNER JOIN categories as cat2 ON cat2.lft <= cat1.lft AND cat2.rgt >= cat1.rgt ORDER BY cat2.lft
28 янв 16, 05:50    [18738551]     Ответить | Цитировать Сообщить модератору
 Re: Nested Sets выборка  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
xew
Нужно использовать UNION только как в нем передать значения cat1.lft и cat1.rgt ?

Нужно использовать рекурсивные cte, если версия позволяет, иначе временную таблицу и циклы.
28 янв 16, 09:37    [18738920]     Ответить | Цитировать Сообщить модератору
 Re: Nested Sets выборка  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
Nested Sets были предложены Joe Celco, чтобы избежать применения рекурсии, временных таблиц и циклов.
Можно найти его статью на русском языке "Деревья в SQL".

Вместо "cat2.lft <= cat1.lft AND cat2.rgt >= cat1.rgt" нужно "c1.lft between c2.lft AND c2.rgt"

Решение:
with categories as (
   select *
     from (values (0,      null, 'root',   1,  12,    0),
                  (1,         0, 'cat 1',  2,   7,    1),
                  (2,         0, 'cat 2',  8,  11,    1),
                  (3,         1, 'cat 11', 3,   4,    2),
                  (4,         1, 'cat 12', 5,   6,    2),
                  (5,         2, 'cat 21', 9,  10,    2)
          ) as  T(id, parent_id,    name, lft, rgt, lvl)
)
--select replicate('|     ',lvl)+name from categories order by lft;
, products as (
   select *
     from (values(1, 'Продукт 1'),
                 (2, 'Продукт 2'),
                 (3, 'Продукт 3'),
                 (4, 'Продукт 4')
          ) as T(id, name)--, description, parameters, image, price)
)
, category_product as (
   select *
     from (values(1,3),
                 (2,3),
                 (3,4),
                 (4,5),
                 (2,5),
                 (4,3)
          ) as T(product_id, category_id)
)

select p.name as produktName, c2.name as catName--, *
  from categories       c1 
  join categories       c2 on c1.lft between c2.lft and c2.rgt
  join category_product cp on cp.category_id = c1.id 
  join products         p  on p.id=cp.product_id
 where c2.parent_id is not null
 order by product_id, c2.lft;


Результат:

produktName	catName
Продукт 1 cat 1
Продукт 1 cat 11
Продукт 2 cat 1
Продукт 2 cat 11
Продукт 2 cat 2
Продукт 2 cat 21
Продукт 3 cat 1
Продукт 3 cat 12
Продукт 4 cat 1
Продукт 4 cat 11
Продукт 4 cat 2
Продукт 4 cat 21
1 фев 16, 17:45    [18758066]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить