Я разрабатываю 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 :))
> Помогите пожалуйста смоделировать нужный запрос, результатом которого была бы выборка
> из таблицы с 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)
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() :))
Видать одной книгой в таком деле не обойтись.
> Я разрабатываю N-мерный каталог продукции, то меж каталог с подкатегориями неограниченной вложенностиПосмотрите в сторону "Дерево каталогов NESTED SETS (вложенные множества)"
Например: http://www.getinfo.ru/article610.html
> Посмотрите в сторону "Дерево каталогов 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 нужно указать имя столбца. причём да же равенство двух столбцов ?Если не сложно, объясните каким образом работает это внешнее левое соединение?
> Но вот не могу понять досих пор одного... Как этот запрос работает?
> Или точнее каким образам происходит внешнее левое объединение по условию,ON
> FIND_IN_SET(t1.id,t2.pid)Ведь результатом этой функции простое число, соответствующее
> позиции подстроки t1.id в t2.pid строке. А в ON нужно указать
> имя столбца. причём да же равенство двух столбцов ?
> Если не сложно, объясните каким образом работает это внешнее левое соединение?В ON указывается УСЛОВИЕ соединения, а не "равенство двух столбцов". Т.к. FIND_IN_SET() возвращает 0 если элемента нет в списке и >0 значение если элемент найден, то эту функцию можно использовать как условную т.к. 0 == FALSE, !0 == TRUE.
Где-то так. :)
> В ON указывается УСЛОВИЕ соединения, а не "равенство двух столбцов". Т.к. FIND_IN_SET()
> возвращает 0 если элемента нет в списке и >0 значение если
> элемент найден, то эту функцию можно использовать как условную т.к. 0
> == FALSE, !0 == TRUE.
> Где-то так. :)Ага... ясно! Всё оказывается так просто! :))
Большое вам спасибо! :))