Как сделать MySql выборку из несколькиз таблиц?

Question

[Александр]

Здравствуйте.

Есть такая схема таблиц:


Нужно:
Составить запрос так, чтобы отобрать все записи из таблицы Resources со значениями из таблицы Variable_values.

Результат должен выглядеть примерно так:

resource
----id
----title
----variables
--------variable
------------id
------------title
------------values
----------------value
----------------value
----------------value
--------variable
------------id
------------title
------------values
----------------value
----------------value
----------------value
--------variable
------------id
------------title
------------values
----------------value
----------------value
----------------value
----childs
--------resource
------------id
------------title
------------variables
----------------variable
--------------------id
--------------------title
--------------------values
------------------------value
------------------------value
------------------------value
------------childs
и т.д.

Таблица Resources древовидная. Глубина не ограничена. Но для моей задачи мне нужно выбрать все записи, у которых соблюдены такие условия и вложенность:
Запись с шаблоном Модель
----Запись с шаблоном Дизайн модели
--------Запись с шаблоном Серия дизайна

То есть глубина вложенности 3. id шаблонов известны изначально.

С этой задачей я могу справиться так:

SELECT * FROM Resources AS Models
LEFT JOIN Resources AS Designs ON Designs.parent_id = Models.id
LEFT JOIN Resources AS Batches ON Batches.parent_id = Designs.id
WHERE Designs.template_id = id шаблона дизайна модели
AND WHERE Batches.template_id = id шаблона серии дизайна

А вот дальше как присоединить переменные (Variables) и их значения (Variable_values) не понимаю. Джойнить выходит очень накладно по времени выполнения запроса.

Запрос хотелось бы иметь всего один, так как необходимо использовать LIMIT на выборку, а также разные условия для выборки (Where).

Подскажите, пожалуйста, какие варианты вы видите.

Comments

[alexalexes]

MySQL какой версии?

[Slava Rozhnev]

Было бы гораздо проще если бы Вы вылохили структуру таблиц и примерные данные на SQL fiddle

[Александр]

alexalexes, MySql 8.0.27

[Алексей Горбунов]

https://dev.mysql.com/doc/refman/8.0/en/with.html#...

[Александр]

Slava Rozhnev,
https://sqlize.online/?phpses=null&sqlses=8085791a...

[Александр]

Алексей Горбунов, рекурсия поможет найти 3 уровня вложенности таблицы Resources (это можно сделать и джойнами, рекурсия лусше?). А как правильно присоединить к уже выбранным строкам из Resources строки из таблиц Variables и Variable_values? Или я не правильно Вас понял?

[Slava Rozhnev]

Александр, Возможно так

Answer 1

[AUser0]

SELECT * FROM Resources AS Models
LEFT JOIN Resources AS Designs ON (Designs.parent_id = Models.id AND Designs.template_id = id шаблона дизайна модели)
LEFT JOIN Resources AS Batches ON (Batches.parent_id = Designs.id AND Batches.template_id = id шаблона серии дизайна)
WHERE Models.parent_id IS NULL
AND Designs.id IS NOT NULL
AND Batches.id IS NOT NULL

Теперь приклеивайте к этому запросу JOIN-ы из переменных.

Comments

[Алексей Горбунов]

Вот зачем вы людей запутываете такими ответами?

Пусть открывает мануал, находит WITH RECURSIVE и получает иерархию любой вложенности.

[Александр]

Ну в общем я так и начал сначала делать.
То есть чтобы добраться до строк из Variable_values нужно присоединить Template_variables, затем Variables и затем только Variable_values. Если сразу приджойнить таблицу Variable_values, то есть вероятность, что мы заберём те строки из Variables, которых нет в строках Template_variables.
Но такое количество джойнов, даже с LIMIT 10 выходит очень медленно. Либо я что-то не так делаю. Делаю везде LEFT JOIN.

[Алексей Горбунов]

Александр, В MySQL join реализован не таким образом как в других бд. Грубо говоря вместо слияния деревьями там внутри вложенные циклы. Добавляя каждый новый джойн ты увеличиваешь количество вложенных циклов. LIMIT тебе слабо поможет, т.к. сначала выполняется запрос и только после выполнения отбирается нужное количество строк. Я тебе оставил ссылку на доки с примерами, почитаешь, разберешься и сделаешь правильно.