Я не уверен, что это возможно в mySQL. Вот мои таблицы: –
Таблица категорий:
Я использую приведенную выше таблицу для сопоставления всех категорий и подкатегорий.
Таблица продуктов:
Category_id в таблице «Продукты» указывает на идентификатор подкатегории, в котором он принадлежит.
например, если у меня есть Игрушки> Образовательные> ABC, где ABC является продуктом, Toys is Category и Educational – это подкатегория, тогда ABC будет иметь category_id как 2.
Теперь проблема в том, что я хочу использовать SQL-запрос для отображения всех продуктов (во всех подкатегориях и их подкатегориях. N уровня) для определенной категории.
например:
select * from categories,products where category.name = 'Toys' and .... В вышеуказанном запросе должны отображаться продукты из образовательных учреждений и всех других подкатегорий и их подкатегорий.
Возможно ли это с помощью запроса mySQL? Если нет, то какие у меня варианты? Я бы хотел избежать рекурсии PHP.
Обновление. В основном я хочу отобразить 10 лучших продуктов в основной категории, которые я буду делать, добавив столбец «хиты» в таблицу продуктов.
Что я сделал в предыдущих проектах, где мне нужно было сделать то же самое, я добавил два новых столбца.
И затем я добавил триггер в таблицу, в которой содержится информация о категории, чтобы сделать следующее (все три обновления находятся в одном и том же триггере) …
-- Reset all branches UPDATE t_org_branches SET nvc_breadcrumb = NULL, i_depth = NULL -- Update the root branches first UPDATE t_org_branches SET nvc_breadcrumb = '/', i_depth = 0 WHERE guid_branch_parent_id IS NULL -- Update the child branches on a loop WHILE EXISTS (SELECT * FROM t_branches WHERE i_depth IS NULL) UPDATE tobA SET tobA.i_depth = tobB.i_depth + 1, tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim(tobA.guid_branch_parent_id) + '/' FROM t_org_branches AS tobA INNER JOIN t_org_branches AS tobB ON (tobA.guid_branch_parent_id = tobB.guid_branch_id) WHERE tobB.i_depth >= 0 AND tobB.nvc_breadcrumb IS NOT NULL AND tobA.i_depth IS NULL
А затем просто присоединитесь к своей таблице продуктов в идентификаторе категории и сделайте «LIKE»% / [CATEGORYID] /% '». Имейте в виду, что это было сделано в MS SQL, но его достаточно просто перевести в версию MySQL.
Он может быть достаточно совместим для вырезания и вставки (после изменения имени таблицы и столбца).
Расширение объяснения …
t_categories (как это сейчас) …
Cat Parent CategoryName 1 NULL MyStore 2 1 Electronics 3 1 Clothing 4 1 Books 5 2 Televisions 6 2 Stereos 7 5 Plasma 8 5 LCD
t_categories (после модификации) …
Cat Parent CategoryName Depth Breadcrumb 1 NULL MyStore NULL NULL 2 1 Electronics NULL NULL 3 1 Clothing NULL NULL 4 1 Books NULL NULL 5 2 Televisions NULL NULL 6 2 Stereos NULL NULL 7 5 Plasma NULL NULL 8 5 LCD NULL NULL
t_categories (после использования сценария, который я дал)
Cat Parent CategoryName Depth Breadcrumb 1 NULL MyStore 0 / 2 1 Electronics 1 /1/ 3 1 Clothing 1 /1/ 4 1 Books 1 /1/ 5 2 Televisions 2 /1/2/ 6 2 Stereos 2 /1/2/ 7 5 LCD 3 /1/2/5/ 8 7 Samsung 4 /1/2/5/7/
t_products (как у вас сейчас, без изменений) …
ID Cat Name 1 8 Samsung LNT5271F 2 7 LCD TV mount, up to 36" 3 7 LCD TV mount, up to 52" 4 5 HDMI Cable, 6ft
Присоединяйтесь к категориям и продуктам (где категории C, продукция P)
C.Cat Parent CategoryName Depth Breadcrumb ID p.Cat Name 1 NULL MyStore 0 / NULL NULL NULL 2 1 Electronics 1 /1/ NULL NULL NULL 3 1 Clothing 1 /1/ NULL NULL NULL 4 1 Books 1 /1/ NULL NULL NULL 5 2 Televisions 2 /1/2/ 4 5 HDMI Cable, 6ft 6 2 Stereos 2 /1/2/ NULL NULL NULL 7 5 LCD 3 /1/2/5/ 2 7 LCD TV mount, up to 36" 7 5 LCD 3 /1/2/5/ 3 7 LCD TV mount, up to 52" 8 7 Samsung 4 /1/2/5/7/ 1 8 Samsung LNT5271F
Теперь, полагая, что таблица продуктов была более полной, так что в каждой категории есть вещи и нет NULL, вы можете сделать «Breadcrumb LIKE»% / 5 /% '», чтобы получить последние три элемента последней таблицы, которые я предоставил. Обратите внимание, что в него входят прямые предметы и дети категории (например, телевизор Samsung). Если вы хотите ТОЛЬКО конкретных предметов категории, просто сделайте «c.cat = 5».
Я думаю, что самым чистым способом добиться этого было бы использование модели вложенных множеств . Это немного сложно реализовать, но мощно использовать. В MySQL есть учебник под названием « Управление иерархическими данными в MySQL» . Один из больших гуру SQL Joe Celko написал о том же самом здесь . Если вам нужна еще дополнительная информация, посмотрите ссылки Troel на хранение иерархических данных .
В моем случае я бы держался подальше от использования РСУБД для хранения данных такого типа и вместо этого использовал базу данных графов, так как данные в этом случае фактически являются ориентированным графиком.
Добавьте столбец в таблицу «Категории», которая будет содержать полное дерево с разделителями-запятыми для каждой группы. Используя ваш пример, подкатегория Education имела бы это как дерево «1,2», где 1 = Toys, 2 = Educational (он включает в себя). Следующий вложенный уровень категорий будет продолжать добавлять к дереву.
Чтобы получить все продукты в группе, вы используете функцию FIND_IN_SET MySQL, например
SELECT p.ID FROM Products p INNER JOIN Categories c ON p.category_ID = c.ID WHERE FIND_IN_SET(your_category_id, c.tree)
Я бы не использовал этот метод для больших таблиц, так как я не думаю, что этот запрос может использовать индекс.
Один из способов – поддерживать таблицу, которая содержит отношения предка к потомкам. Вы можете запросить эту конкретную таблицу и получить список всех иждивенцев.
Предполагая, что MySQL будет трудно избежать рекурсии в PHP.
Ваш вопрос, по сути, заключается в том, как имитировать синтаксис CONNECT BY PRIOR Oracle в MySQL. Люди задают этот вопрос неоднократно, но это функция, которая никогда не попадала в MySQL, и реализация через хранимые процедуры, вероятно, не будет работать, потому что (сейчас) хранимые функции не могут быть рекурсивными .
Остерегайтесь до сих пор доступными клопами базы данных.
Самая лучшая информация до сих пор – три ссылки от nawroth :
Насколько велики таблицы категорий? Возможно, вам нужно кэшировать это на уровне приложения и построить соответствующий запрос: … где id в (2, 3, 6, 7)
Кроме того, лучше всего выбрать категории по идентификатору, который является их уникальным идентификатором, индексированным и быстрым, а не поиском по имени.
Горе со мной, потому что я никогда не делал ничего подобного.
BEGIN SET cat = "5"; SET temp = ""; WHILE STRCMP(temp, cat) != 0 DO SET temp = cat; SET cat = SELECT CONCAT_WS(GROUP_CONCAT(id), cat) FROM Categories GROUP BY (parent_id) HAVING FIND_IN_SET(parent_id, cat); END LOOP; END; SELECT * FROM products WHERE FIND_IN_SET(category_id, cat)
Я почти гарантирую, что вышеуказанное не будет работать, но вы можете видеть, что я пытаюсь сделать. Я добрался так далеко, и я решил не закончить окончание запроса (выберите верхнюю N из каждой категории), извините. :П