Sql запрос поиска в дереве

382

18 февраля 2017, 05:58

Нужно составить sql запрос, с помощью которого можно узнать входит ли пользователь в ветку партнёров.

Поясняю: допустим есть пользователь A, у которого есть партнёры B, C, D, - у этих партнёров свои партнёры и так далее. В итоге получается дерево.

К примеру: есть такая ветка партнёров: A -> C -> F -> H -> P, нужно узнать входит ли пользователь H в ветку где один из предшественников - пользователь C.

Максимальный уровень вхождения для поиска в ветке = 10, но, возможно будет больше или меньше.

Описание полей таблицы:

id - ID записи партнёра
user_id - ID пользователя
partner_id - ID партнёра пользователя


CREATE TABLE IF NOT EXISTS `partners` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `partner_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partners_user_id_partner_id_unique` (`user_id`,`partner_id`),
  KEY `partners_user_id_partner_id_index` (`user_id`,`partner_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

P.S. Mysql v5.5

Answer 1

На этот случай можно хранить иерархию в строке, как вы и написали, через разделитель, для каждого пользователя. В этом случае, определив ветку партнёра, вы сможете составить RLIKE или LIKE выражение на вхождение строкового значения из корневой ветки. Это называется материализованное значение пути (МТЗ), и вот его пример из ссылки:

ID | NAME                     | PATH
100 | Electronics & Computers | /100
101 | Games                   | /100/101
102 | Xbox360                 | /100/101/102
103 | PS4                     | /100/101/103

Запросы к таблице будут выглядеть так:

-- Найти детей игрушек (Games):
SELECT * FROM catalog WHERE path LIKE “/100/101/%”
-- Изменить родителя:
UPDATE catalog SET path = REPLACE(path, ‘/100/101’, ‘/200/201’) WHERE path LIKE ‘/100/101/%’