Остановить рекурсивное кровосмесительное отношение родительских родителей в mysql

Я программирую в PHP/MySQL/Javascript. У меня есть список частей, которые мы хотим связать в отношении child/parent без ограничения количества ярусов.

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

Что я обнаружил, так это то, что я также хочу исключить бабушек и дедушек родителя, так как иначе мы можем получить кровосмесительные отношения, которые при отображении дерева частей создадут бесконечный цикл.

Не только это, но я не могу допустить, чтобы дочерняя часть была великим дедушкой или родителем родителя или великого прародителя e.t.c.

Вот оператор SQL, который я использую в настоящее время, который, как я думаю, также может быть улучшен с помощью LEFT JOIN, но я недостаточно хорошо разбираюсь в SQL на этом этапе.

SELECT * 
FROM sch_part_general 
WHERE (sch_part_general.part_id <> $parentId) 
AND (sch_part_general.part_id NOT IN 
  (SELECT part_id FROM sch_part_mapping WHERE parent_id = $parentId)
)

sch_part_general - таблица с несколькими столбцами со всеми частями, а part_id - как первичный ключ. sch_part_mapping - таблица отображения двух столбцов с part_id (child) || parent_id (родительский).

Может ли кто-нибудь указать мне в правильном направлении с SQL-запросом? Я не заинтересован в том, чтобы использовать цикл while для создания SQL-оператора, поскольку я думаю, что это будет довольно неэффективно, но это единственный способ, который, как я считал, может работать до сих пор.

Ответ 1

MySQL не имеет большой (если есть) поддержки иерархических запросов. Если вы хотите придерживаться того, что называется Adjacency List Model, все, что вы можете сделать, это добавить JOIN для каждого уровня, который вы хотите включить. Излишне говорить, что это плохо масштабируется.

С другой стороны, если вы можете изменить схему базы данных, я бы предложил реализовать Nested Set Model.

Очень хорошее объяснение Nested Set Model представлено в блоге Майка Хиллиера

Ограничения модели списка смежности

Работа с моделью списка смежности в чистом SQL может быть затруднена при Лучший. Прежде чем вы сможете увидеть полный путь к категории, мы должны знать уровень, на котором он проживает.

Вложенная модель набора

понятие вложенных множеств в SQL существует уже более десятилетия, и есть много дополнительной информации, доступной в книгах и на интернет. По моему мнению, самый полный источник информация об управлении иерархической информацией - это книга под названием Джо Деревья Celkos и иерархии в SQL для Smarties, написанные очень уважаемый автор в области продвинутого SQL, Джо Целько.

Ответ 2

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

если вы можете изменить схему, тогда, возможно, для вашего дела может быть достаточно следующего: добавьте новый столбец в sch_part_mapping, давайте назовем его "hierarchy_id". это значение, построенное как уникальное int в первый раз, когда вы начинаете совершенно новую иерархию (с первым величайшим величайшим величайшим родителем в любой иерархии, но его сказано на английском языке) и вставляется во все строки, принадлежащие одной иерархии, нет вопрос на каком уровне.

то легко пропустить родителей и внуков, найденных в одной иерархии: к вашему sql выше вы можете добавить:

SELECT * 
FROM sch_part_general 
WHERE (sch_part_general.part_id <> $parentId) 
AND (sch_part_general.part_id NOT IN 
  (SELECT part_id FROM sch_part_mapping WHERE parent_id = $parentId)

//addition here 
and not exists (select * from sch_part_mapping where hierarchy_id= ? and parent_id = sch_part_general.part_id)

)

знак вопроса следует заменить соответствующим идентификатором иерархии, который вам нужно вычислить.

РЕДАКТИРОВАТЬ: я пропустил, что у вас есть переменная для определенного родительского идентификатора, поэтому иерархия_ид может быть рассчитана в том же запросе:

SELECT * 
    FROM sch_part_general 
    WHERE (sch_part_general.part_id <> $parentId) 
    AND (sch_part_general.part_id NOT IN 
      (SELECT part_id FROM sch_part_mapping WHERE parent_id = $parentId)

    //addition here 
    and not exists (select * from sch_part_mapping where hierarchy_id= (select hierarchy_id from sch_part_mapping where parent_id = $parentId limit 1) and parent_id = sch_part_general.part_id)


)

Ответ 3

С MySql/MariaDB вы можете использовать движок Open Query Graph (http://openquery.com/graph/doc), который является плагином mysql, который позволяет создавать специальные таблицу, где вы устанавливаете отношения, в основном parentId и childId.

Магия в том, что вы запрашиваете эту таблицу со специальной защелкой столбца в зависимости от значения, переданного в запросе, укажет движку OQGRAPH, какую команду выполнить. Подробнее см. В документах.

Он обрабатывает не только дерево (рекурсивные отношения 1-n), но и структуры графических данных (рекурсивные отношения nm) с весом (например, подумайте, что вы хотите сохранить права собственности компаний, компания может иметь несколько дочерних компаний и может также иметь несколько акционеры).