Xpoint
   [напомнить пароль]

Снова про дерево Nested Sets

Метки: [без меток]
2008-03-27 21:45:22 [обр] Игорь Косолапов[досье]

Добрый вечер!
Никак не могу решить задачку. Очень расчитываю на вашу помощь.

СУБД MySQL v.5.0.45, три таблицы:

  1. собственно само дерево - categories(cid, cleft, cright, clevel);
  2. информация о товарах - main(idd - уникальный идентификатор товара, name - название, date - дата добавления в базу);
  3. таблица, с информацией о том, к каким категориям относятся товары - link(idd - идентификатор товара, idc - идентификатор категории).

Задача: получить все категории (отсортированные по cleft), в которых содержатся товары, добавленные в базу после определенной даты (например, с начала года). Т.е. необходимо сформировать дерево категорий по новым товарам. И еще момент, напротив каждой категории должно стоять число новых товаров относящихся к ней.

В SQL не силен, поэтому обращаюсь с просьбой о помощи к более компетентным коллегам. Товарищи, помогите пожалуйста, уже всю голову себе изломал.

спустя 11 часов [обр] Александр Галкин(0/211)[досье]
SELECT c.*, count(m.idd)
FROM categories c
INNER JOIN link l ON l.idc = c.cid
INNER JOIN main m ON m.idd = l.idd AND m.date > ...
GROUP BY c.cid
ORDER BY c.cleft
спустя 36 минут [обр] Игорь Косолапов[досье]

Александр Галкин[досье], спасибо огромное за желание помочь!

При таком запросе, мы получим только список категорий непосредственно содержащих новые товары, но не получим верхние узлы дерева, т.е. родителей. Таблица link содержит категории, которые непосредственно содержат товары.
Информацию о родителях можно извлечь только из таблицы categories, используя cleft и cright потомка, содержащего товар. В этом-то вся и сложность.

спустя 36 минут [обр] Александр Галкин(0/211)[досье]
Игорь Косолапов[досье]
Ой, точно, не подумал об этом.
Ну, в любом случае, этим запросом мы получаем список категорий, содержащих товары. Можно туда дописать теперь выбор всех остальных категорий — если не ошибаюсь, как-то так:
SELECT distinct c.*
FROM categories c
INNER JOIN (
   SELECT c.cleft, c.cright
   FROM categories c
   INNER JOIN link l ON l.idc = c.cid
   INNER JOIN main m ON m.idd = l.idd AND m.date > ...
) t
WHERE c.cleft <= t.cleft AND c.cright >= t.cright
ORDER BY c.cleft
спустя 2 часа 49 минут [обр] Игорь Косолапов[досье]

Александр Галкин[досье], спасибо огромное! Вы мне очень помогли.

Напишу то, что в итоге получилось. Вдруг кому пригодится.

Исходные таблицы:

  1. собственно само дерево категорий - categories (cid, cleft, cright, clevel);
  2. названия категорий - cattitle (cid, name);
  3. информация о товарах - main (idd - уникальный идентификатор товара, name - название, date - дата добавления в базу);
  4. таблица, с информацией о том, к каким категориям относятся товары - link (idd - идентификатор товара, cid - идентификатор категории).

Задача: построить дерево категорий, содержащих товары, добавленные после указанной даты. На выходе должны получить:

  1. cid - идентификатор категории;
  2. name - название категории;
  3. clevel - уровень вложенности категории;
  4. count - количество товаров , добавленных после указанной даты.

Собственно решение:

SELECT a.cid, b.name, a.clevel, COUNT(*) AS count
FROM categories a 
INNER JOIN cattitle b USING (cid)
INNER JOIN (
    SELECT c.cleft, c.cright 
    FROM categories c 
    INNER JOIN link l ON l.cid = c.cid
    INNER JOIN main m ON m.idd = l.idd AND m.date > ...
) c 
WHERE a.cleft <= c.cleft AND a.cright >= c.cright  AND a.clevel > 0
GROUP BY a.cid
ORDER BY a.cleft

Условие a.clevel > 0 нужно, чтобы исключить корень дерева.

спустя 56 минут [обр] Dennis F. Latypoff aka funky_dennis(0/84)[досье]
а теперь запустите этот запрос миллиард раз хотя бы со ста параллельных клиентов :)
спустя 13 минут [обр] Игорь Косолапов[досье]
Dennis F. Latypoff aka funky_dennis[досье] это Вы к чему? Если есть какие-то претензии к запросу - аргументы в студию.
спустя 3 часа 17 минут [обр] Dennis F. Latypoff aka funky_dennis(0/84)[досье]
EXPLAIN SELECT a.cid, b.name, a.clevel, COUNT(*) AS count
FROM categories a 
INNER JOIN cattitle b USING (cid)
INNER JOIN (
    SELECT c.cleft, c.cright 
    FROM categories c 
    INNER JOIN link l ON l.cid = c.cid
    INNER JOIN main m ON m.idd = l.idd AND m.date > 0
) c 
WHERE a.cleft <= c.cleft AND a.cright >= c.cright  AND a.clevel > 0
GROUP BY a.cid
ORDER BY a.cleft
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARY<derived2>systemNULLNULLNULLNULL1Using temporary; Using filesort
1PRIMARYbALLPRIMARYNULLNULLNULL2
1PRIMARYaeq_refPRIMARY,cright,clevel,cleftPRIMARY4test.b.cid1Using where
2DERIVEDmsystemPRIMARY,dateNULLNULLNULL1
2DERIVEDlconstPRIMARY,cidPRIMARY4const1
2DERIVEDcconstPRIMARYPRIMARY4const1

6 rows in set (0.00 sec)

интересно, что покажет EXPLAIN на Ваших таблицах с Вашими данными.

Powered by POEM™ Engine Copyright © 2002-2005