Иерархические данные в MySQL

Вы хотите получить родительский идентификатор:

Итак, предположим, что вам дали

set @parentId = 1 /*toys*/

select 
  *
from
  Items i
inner join Categories c on c.id = i.categoryId
where
  c.parentId = @parentId

Это даст вам нужные элементы - с одним серьезным недостатком дизайна: он не обрабатывает несколько уровней иерархических категорий.

Допустим, у вас есть таблица категорий:

*Categories table*
id | name    | parentId
1  | Toys    | 0
2  | Dolls   | 1
3  | Bikes   | 1
4  | Models  | 2
5  | Act.Fig.| 2
6  | Mountain| 3
7  | BMX     | 3

и элементы:

*items table*
item   | category_id
Barbie | 4
GIJoe  | 5
Schwinn| 6
Huffy  | 7

Единственный способ получить все соответствующие элементы - выполнить самостоятельное соединение:

select 
  *
from
  Items i 
inner join Categories c on c.id = i.categoryId
inner join Categories c2 on c.parentId = c2.id
where
  c2.parentId = @parentId

Этот шаблон не масштабируется - поскольку у вас может быть НЕСКОЛЬКО уровней иерархии.

Одним из распространенных способов работы с иерархиями является построение «плоской» таблицы: строки, которая связывает каждый узел со ВСЕМИ его потомками.

В дополнение к таблице категорий вы создаете вторую таблицу:

*CategoriesFlat table*  The Name column is here only for readability
id | name    | parentId
1  | Toys    | 1
-----------------
2  | Dolls   | 1
2  | Dolls   | 2
-----------------
4  | Models  | 1
4  | Models  | 2
4  | Models  | 4
5  | Act.Fig.| 1
5  | Act.Fig.| 2
5  | Act.Fig.| 5
-----------------
3  | Bikes   | 1
3  | Bikes   | 3
-----------------
6  | Mountain| 1
6  | Mountain| 3
6  | Mountain| 6
7  | BMX     | 1
7  | BMX     | 3
7  | BMX     | 7

Итак вы можете написать:

select 
  *
from
  Items i
inner join CategoriesFlat c on c.id = i.categoryId
where
  c.parentId = @parentId

И получить ВСЕ соответствующие Категории и Пункты.

Вот отличное слайд-шоу об анти-шаблонах SQL и их решениях.

Но если бы у меня было около 10 категорий в разделе «Игрушки», мне пришлось бы выполнить это соединение и запросить 10 раз. Есть ли лучший способ справиться с этим?

Да, есть способ хранения данных, который называется « вложенные наборы ». Немного сложнее вставить данные, но просто выбрать целую многоуровневую ветвь с помощью одного оператора select .

Кроме того, Celko написал книгу об этом предмет, с главой о вложенных наборах и другими главами о других методах.

Полагаю, вы знаете, как получить идентификационный номер, и вопрос не в этом. Кроме того, parent_id также должен быть FK-ссылкой id , и я бы использовал NULL для самого верхнего уровня, а не 0.

Если ваши самые верхние категории имеют не более одного уровня подкатегории, вы можете использовать этот запрос для получения всех игрушек:

SELECT *
FROM items
WHERE items.category_id IN (SELECT id FROM categories
                            WHERE categories.parent_id = 1
                            OR categories.id = 1);

Если ваши категории могут иметь вложенные подкатегории, вам придется использовать хранимую процедуру и вызывать ее рекурсивно. Псевдокод:

Procedure getItemsInCategory
Input: @category_id integer
Output: items rows
{
    For each item in (SELECT *
                      FROM items
                      WHERE items.category_id = @category_id):
        return the row;

    For each id in (SELECT id 
                    FROM categories
                    WHERE categories.parent_id = @category_id):
        return the rows in getItemsInCategory(id);
}

Предположим, вам известен идентификатор категории «Игрушки», но в категории «Игрушки верхнего уровня» ничего нет:

SELECT * FROM items WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 1)

Эта ветка может помочь: http://forums.mysql.com/read.php?10,32818,32818#msg-32818

На самом деле вам нужно НАЧАТЬ и Синтаксис CONNECT BY, но он поддерживается только в Oracle, но не в MySQL.

Я не знаком с MySQL, но вот как я бы сделал это в TSQL (SQL SERVER), может быть, попытаться найти эквивалентный способ сделать это в MySQL?

1 ) Прокрутите все категории, чтобы получить дочерние элементы для конкретного элемента, в данном случае идентификатор категории = 1

2) Отфильтруйте элементы, относящиеся к дочерним элементам в CTE иерархии (общее табличное выражение).

With Hierarchy As
(

SELECT    id, name, parent_id
from         categories
where        id = 1
UNION ALL
SELECT      child.id, child.name, child.parent_id
from           categories child
inner join  Hierarchy parent on child.parent_id = parent.id
)
SELECT * FROM items
WHERE category_id IN 
(
   Select id
   from Hierarchy 
 )

1. Используйте IN оператор .
2. Используйте хранимую процедуру .
3. Оптимизируйте свои таблицы, чтобы лучше отразить их привычку.