The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

форумы  помощь  поиск  регистрация  майллист  вход/выход  слежка  RSS
"Хитрый SQL запрос для моделирования N-мерного каталога"
Вариант для распечатки  
Пред. тема | След. тема 
Форум WEB технологии (MySQL)
Изначальное сообщение [ Отслеживать ]

"Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от ILYA INDIGO email(ok) on 05-Апр-11, 11:00 
Я разрабатываю N-мерный каталог продукции, то меж каталог с подкатегориями неограниченной вложенности, Задумка такая, что у товара может быть миниум одна, а максиум N (Ну, для начала, N не менее 5-ти, но задумка сделать НЕ ограниченным) и если в чистой категории (pid=0) можно создать или товар или подкатегорию. Если создаём подкатегорию, то этой новосозданной подкатегории в поле pid записывается id родительской категории (род, кат,), и в род, кат, отныне можно создавать только подкатегории (товары уже НЕльзя), до тех пор, пока из неё не будут удалены все подкатегории, А если же мы в род, кат, создаём товар, то в таблице товаров полю link_id созданного товара присваивается id род,кат,, и в ней мы теперь можем создавать только товары, пока не будут из таблицы удалены все товары. Пока стандартная схема 3-ёх уровнего каталога, но фишка в том, что поле pid таблицы категорий не INT, а VARCHAR, и когда мы в категории 2-ого уровня (К2) создаём К3, то в pid записываются через запятую id род, кат, начиная с самой верхней, Томеж если в К1 с id=1 pid=0, создали К2 у которой id=2 и pid=1, и в ней создали К3 у которой id=3, то pid у ней будет равен pid='1,2' и т,д,
Вот упрощённая структура таблицы с тестовым дампом:
CREATE TABLE `test`
(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`pid` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`title` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''
)
ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `test`
(`id`,`pid`,`title`)
VALUES
(1,'0','Кат 1'),
(2,'0','Кат 2'),
(3,'0','Кат 3'),
(4,'1','Кат 1.1'),
(5,'1','Кат 1.2'),
(6,'1','Кат 1.3'),
(7,'1,4','Кат 1.1.1'),
(8,'1,4','Кат 1.1.2'),
(9,'1,4','Кат 1.1.3'),
(10,'1,5','Кат 1.2.1'),
(11,'1,5','Кат 1.2.2'),
(12,'1,5','Кат 1.2.3'),
(13,'1,6','Кат 1.3.1'),
(14,'1,6','Кат 1.3.2'),
(15,'1,6','Кат 1.3.3'),
(16,'2','Кат 2.1'),
(17,'2','Кат 2.2'),
(18,'2','Кат 2.3'),
(19,'0','Кат 4');


Для того, что бы узнать какая категория последняя (не имеющая детей, следовательно или имеющая товары или пустая), а какая родительская (имеющая детей, следовательно имеющая подкатегории) Мне нужно отделить группу "Последних id" (3,7,8,9,10,11,12,13,14,15,16,17,18,19) которые или пустые или содержат товары, и группу "Промежуточных id" (1,2,4,5,6)

А вот теперь самое интересное,,, По моей задумке для выделения "Промежуточноых id" я хотел использовать запрос:
SELECT * FROM `test` WHERE `id` IN(SELECT `pid` FROM `test`);
То меж выделить id, которые указаны в pid причём всё равно в как и в каком порядке, так как id в pid я отделил запятыми, я надеялся что синтаксисом IN подобная запись интерпретируется в последовательность id через запятую, но на практике происходит приведение данных к INT и строка '1,4' преобразовывается в '1' и результат совсем не тот, который я ожидал.
Можно ли что то с этим сделать, или как по другому записать запрос, например используя оператор LIKE ? Ведь в LIKE можно подставлять только значения столбца, а не имя столбца, да ещё и с конкатенацией шаблона, к имени каждого столбца,
Помогите пожалуйста смоделировать нужный запрос, результатом которого была бы выборка из таблицы с id 1,2,4,5,6 :))

Ответить | Правка | Cообщить модератору

Оглавление

Сообщения по теме [Сортировка по времени | RSS]


1. "Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от JohnProfic (ok) on 05-Апр-11, 12:38 
> Помогите пожалуйста смоделировать нужный запрос, результатом которого была бы выборка
> из таблицы с id 1,2,4,5,6 :))

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


mysql> select t1.* from test as t1 left join test as t2 on find_in_set(t1.id, t2.pid) where t2.id is not null group by t1.id;
+----+-----+---------+
| id | pid | title   |
+----+-----+---------+
|  1 | 0   | Кат 1   |
|  2 | 0   | Кат 2   |
|  4 | 1   | Кат 1.1 |
|  5 | 1   | Кат 1.2 |
|  6 | 1   | Кат 1.3 |
+----+-----+---------+
5 rows in set (0.00 sec)
mysql> select t1.* from test as t1 left join test as t2 on find_in_set(t1.id, t2.pid) where t2.id is null group by t1.id;
+----+-----+-----------+
| id | pid | title     |
+----+-----+-----------+
|  3 | 0   | Кат 3     |
|  7 | 1,4 | Кат 1.1.1 |
|  8 | 1,4 | Кат 1.1.2 |
|  9 | 1,4 | Кат 1.1.3 |
| 10 | 1,5 | Кат 1.2.1 |
| 11 | 1,5 | Кат 1.2.2 |
| 12 | 1,5 | Кат 1.2.3 |
| 13 | 1,6 | Кат 1.3.1 |
| 14 | 1,6 | Кат 1.3.2 |
| 15 | 1,6 | Кат 1.3.3 |
| 16 | 2   | Кат 2.1   |
| 17 | 2   | Кат 2.2   |
| 18 | 2   | Кат 2.3   |
| 19 | 0   | Кат 4     |
+----+-----+-----------+
14 rows in set (0.00 sec)

Ответить | Правка | ^ к родителю #0 | Наверх | Cообщить модератору

2. "Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от ILYA INDIGO email(ok) on 05-Апр-11, 13:45 
 

select t1.* from test as t1 left join test as t2 on find_in_set(t1.id, t2.pid) where t2.id is not null group by t1.id;

select t1.* from test as t1 left join test as t2 on find_in_set(t1.id, t2.pid) where t2.id is null group by t1.id;

:) Большое вам спасибо! :)

Прочитал книгу Мартина Грабера - "SQL" (правда там рассматривались только SQL92 и SQL99), и думал, что знаю все инструменты SQL, и даже не знал про существования операторов FIELD() и FIND_IN_SET() :))
Видать одной книгой в таком деле не обойтись.

Ответить | Правка | ^ к родителю #1 | Наверх | Cообщить модератору

3. "Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от ymkin (??) on 11-Апр-11, 15:40 
> Я разрабатываю N-мерный каталог продукции, то меж каталог с подкатегориями неограниченной вложенности

Посмотрите в сторону "Дерево каталогов NESTED SETS (вложенные множества)"
Например: http://www.getinfo.ru/article610.html

Ответить | Правка | ^ к родителю #0 | Наверх | Cообщить модератору

4. "Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от ILYA INDIGO email(ok) on 11-Апр-11, 16:29 
> Посмотрите в сторону "Дерево каталогов NESTED SETS (вложенные множества)"
> Например: http://www.getinfo.ru/article610.html

Спасибо вам за совет, в будущем буду иметь ввиду, но пока это решение содержит для меня большую избыточность (иерархия задаётся аж 3-мя столбцами).
В моём случае, когда иерархия категорий жёстко фиксируется при создании и, впредь, НЕ изменяется, моё решение более простое и менее ресурсоёмкое, но всё равно спасибо :))

Кстати, N-мерный каталог я уже реализовал, и он прекрасно, ведёт себя!

Его основа админской части:


function list_cat($pid)
{
    if ((int)$pid <= 0) $pid = 0;
    $query = 'SELECT t1.*,COUNT(t2.pid)col FROM '.$this->table.'_cat t1 LEFT JOIN '.$this->table.'_cat t2 ON FIND_IN_SET(t1.id,t2.pid) WHERE t1.pid=\''.addslashes($pid).'\' GROUP BY t1.id ORDER BY t1.order';
    $res = $this->db->assoc($query);
    return (is_array($res) && count($res) > 0) ?$res :false;
}

Где разделения на "Конечные" и "Промежуточные" категории происходит путём подсчёта дочерних подкатегорий.

И его основа клиентской части:

function list_cat($id)
{
    $query = 'SELECT t1.*,COUNT(t2.pid)col FROM '.$this->table.'_cat t1 LEFT JOIN '.$this->table.'_cat t2 ON FIND_IN_SET(t1.id,t2.pid) WHERE (t1.pid=\''.(int)$id.'\' OR t1.pid LIKE \'%,'.(int)$id.'\') AND t1.status>0 GROUP BY t1.id ORDER BY t1.order';
    $res = $this->db->assoc($query);
    return (is_array($res) && count($res) > 0) ?$res :false;
}

Где, в отличии от админской части, выполняется только просмотр, а следовательно нам не нужно знать полностью pid родителя и можно схитрить с оператором LIKE, что бы делать выборку не по всему pid, а только по его числовой концовке, что даёт возможность применить ЧПУ к сылкам (когда мы делаем выборку не по 1-му id а по связки id через запятую, нам невозможно заменить его через .htaccess)

Но вот не могу понять досих пор одного... Как этот запрос работает? Или точнее каким образам происходит внешнее левое объединение по условию,

ON FIND_IN_SET(t1.id,t2.pid)
Ведь результатом этой функции простое число, соответствующее позиции подстроки t1.id в t2.pid строке. А в ON нужно указать имя столбца. причём да же равенство двух столбцов ?

Если не сложно, объясните каким образом работает это внешнее левое соединение?

Ответить | Правка | ^ к родителю #3 | Наверх | Cообщить модератору

5. "Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от JohnProfic (ok) on 11-Апр-11, 19:51 
> Но вот не могу понять досих пор одного... Как этот запрос работает?
> Или точнее каким образам происходит внешнее левое объединение по условию,
ON 
> FIND_IN_SET(t1.id,t2.pid)
Ведь результатом этой функции простое число, соответствующее

> позиции подстроки t1.id в t2.pid строке. А в ON нужно указать
> имя столбца. причём да же равенство двух столбцов ?
> Если не сложно, объясните каким образом работает это внешнее левое соединение?

В ON указывается УСЛОВИЕ соединения, а не "равенство двух столбцов". Т.к. FIND_IN_SET() возвращает 0 если элемента нет в списке и >0 значение если элемент найден, то эту функцию можно использовать как условную т.к. 0 == FALSE, !0 == TRUE.
Где-то так. :)

Ответить | Правка | ^ к родителю #4 | Наверх | Cообщить модератору

6. "Хитрый SQL запрос для моделирования N-мерного каталога"  +/
Сообщение от ILYA INDIGO email(ok) on 12-Апр-11, 12:38 
> В ON указывается УСЛОВИЕ соединения, а не "равенство двух столбцов". Т.к. FIND_IN_SET()
> возвращает 0 если элемента нет в списке и >0 значение если
> элемент найден, то эту функцию можно использовать как условную т.к. 0
> == FALSE, !0 == TRUE.
> Где-то так. :)

Ага... ясно! Всё оказывается так просто! :))
Большое вам спасибо! :))

Ответить | Правка | ^ к родителю #5 | Наверх | Cообщить модератору

Архив | Удалить

Рекомендовать для помещения в FAQ | Индекс форумов | Темы | Пред. тема | След. тема




Спонсоры:
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2021 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру