Из двух запросов в один по заранее известным отношениям

@sqlnub · 15.10.2013, 16:51. **Показов** 916. **Ответов** 6

Студворк — интернет-сервис помощи студентам

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

Состав прост как три копья:

table_name,
column_name,
referenced_table_name,
referenced_column_name
substitute_column_name - самая мякотка, собственное примитивное изобретение.

Благодаря которому в любую таблицу подставляются списками (типа enum) значения указанного поля из связанной таблицы. При поддержке ПО, конечно. Вторая фича, что легко построить запрос на объединение, это самоочевидно в перечисленных сущностях. Короче все шикарно за исключением особого случая - соединения таблиц N:N. В режиме подставновки списков все работает, но запросов получается два штуки, поскольку родитель ничего не знает о потомке, потомок ничего не знает о родителе и все идет через связывающую таблицу данные в которой не человекочитаемые.

В общем вот два сгенеренных ПО запроса:

SQL
1
2
SELECT * FROM `Каталог` JOIN `Продукты_Каталога` ON `Каталог`.`Код_каталога` = `Продукты_Каталога`.`Код_каталога`;
SELECT * FROM `Продукт` JOIN `Продукты_Каталога` ON `Продукт`.`Код_продукта` = `Продукты_Каталога`.`Код_продукта`;

Существует ли абстрактный метод объединения запросов в один, чтобы получилось нечто типа

SQL
1
2
3
4
SELECT `Каталог`. * , `Продукт`. *
FROM `Продукты_Каталога`
INNER JOIN `Каталог` USING ( `Код_каталога` )
INNER JOIN `Продукт` USING ( `Код_продукта` );

Можно без USING(), конечно.

У меня идей нет. Может кто сталкивался и знает как.

@cygapb-007 · 15.10.2013, 18:36

Тупо в лоб не подходит?

Правда, MS SQL, но идея понятна:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
declare @sql varchar(max)=''
;WITH MyTable AS (SELECT * FROM (VALUES
    ('`Каталог`','`К_Код_каталога`','`Продукты_Каталога`','`ПК_Код_каталога`',''),
    ('`Продукт`','`Д_Код_продукта`','`Продукты_Каталога`','`ПК_Код_продукта`',''),
    ('`Продукт`','`Д_Код_производителя`','`Производитель`','`П_Код_производителя`','')
    )cte_MyTable(table_name,column_name,referenced_table_name,referenced_column_name,substitute_column_name))
SELECT @sql+='SELECT a.*, b.*
FROM '+u.table_name+' u
JOIN '+u.referenced_table_name+' a ON a.'+u.referenced_column_name+'=u.'+u.column_name+'
JOIN '+u.rreferenced_table_name+' b ON b.'+u.rreferenced_column_name+'=u.'+u.rcolumn_name+';
 
'
FROM (
   SELECT l.table_name, l.referenced_table_name, l.referenced_column_name, l.column_name, r.referenced_table_name rreferenced_table_name, r.referenced_column_name rreferenced_column_name, r.column_name rcolumn_name
      FROM MyTable l
      JOIN MyTable r on r.table_name=l.table_name and r.referenced_table_name<l.referenced_table_name
      --where l.table_name='`Продукты_Каталога`'
   UNION ALL
   SELECT l.table_name, l.referenced_table_name, l.referenced_column_name, l.column_name, r.table_name, r.column_name, r.referenced_column_name
      FROM MyTable l
      JOIN MyTable r on r.table_name=l.referenced_table_name and r.referenced_table_name<l.table_name
      --where l.table_name='`Продукты_Каталога`'
   UNION ALL
   SELECT l.referenced_table_name, l.table_name, l.column_name, l.referenced_column_name, r.table_name, r.column_name, r.referenced_column_name
      FROM MyTable l
      JOIN MyTable r on r.table_name=l.referenced_table_name and r.table_name<l.referenced_table_name
      --where l.referenced_table_name='`Продукты_Каталога`'
   UNION ALL
   SELECT l.referenced_table_name, l.table_name, l.column_name, l.referenced_column_name, r.table_name, r.column_name,r.referenced_column_name
      FROM MyTable l
      JOIN MyTable r on r.referenced_table_name=l.referenced_table_name and r.table_name<l.table_name
      --where l.referenced_table_name='`Продукты_Каталога`'
   )u
print @sql

Результат:

MySQL
1
2
3
4
5
6
7
8
9
SELECT a.*, b.*
FROM `Продукт` u
JOIN `Производитель` a ON a.`П_Код_производителя`=u.`Д_Код_производителя`
JOIN `Продукты_Каталога` b ON b.`ПК_Код_продукта`=u.`Д_Код_продукта`;
 
SELECT a.*, b.*
FROM `Продукты_Каталога` u
JOIN `Продукт` a ON a.`Д_Код_продукта`=u.`ПК_Код_продукта`
JOIN `Каталог` b ON b.`К_Код_каталога`=u.`ПК_Код_каталога`;

@sqlnub · 15.10.2013, 20:43 **[ТС]**

Названия полей здесь вещь сугубо условная. Это могут быть любые, заранее неизвестные. Известны только отношения, поскольку они прописаны в таблице отношений. Теоретически признаком по которому надо вообще смотреть есть ли там отношение многие ко многим - это повтор того же самого имени в поле table_name. Но признак жидкий какой-то, я подумал что можно найти более жесткий.

И вообще, я похоже немного промахнулся. Надо было в PHP и базы данных, поскольку решение подразумевается процедурное.

Тем не менее спасибо.

Добавлено через 7 минут
Да, идею вроде уловил, но выловить не могу. Вторая часть с union'ами на что-то полезное намекает, вроде. А, и еще, процедуры, в смысле "хранимки", реально использовать не могу, особенности хостинга (не льзя), но перевести на php смог бы, если бы понял смысл.

Добавлено через 5 минут
На самом деле было так. Я сделал поддержку списков и был удовлетворен, но как обычно внезапно захотелось вывести и слитно. Подумал "сейчас пропишу на все отношения готовый запрос, добавить еще поле пара пустяков" и прежде чем все сделал заметил что отношения и так уже прошиты. Более того, описанная таблица строится в модели nested set, то есть имеет ключи указывающие на иерархию. Я это не юзаю, но заложил на будущее. Не юзаю потому что опять-таки не осилил как соединять два или больше простых запросов в один. То есть кроме N:N еще может быть каноничный 1:N с несколькими join'ами, по глубине вложения. Которая вычисляется несусветным запросом, который я никогда в жизни сам бы не придумал. И вообще написав вполне годный класс для этой модели я так и не понял как она работает в целом.

Добавлено через 7 минут
Походу я запарился. Сейчас проверить не могу, но о идее достаточно выкинуть select ... from, то есть:

SQL
1
2
SELECT * FROM table1 JOIN table2 ON table1.id = table2.parent_id;
SELECT * FROM table2 JOIN table3 ON table2.id = table3.parent_id;

это же:

SQL
1
2
SELECT * FROM table1 JOIN table2 ON table1.id = table2.parent_id
JOIN table3 ON table2.id = table3.parent_id;

Или нет?

Добавлено через 5 минут
Про fk отдельно, чтобы не плодить темы по пустякам. Чем объясняется логика каскадного удаления всех потомков при удалении родителя я так и не понял. Это же позднейшее изобретение в такой БД как файловая система - что можно удалить не пустую папку со всем ее барахлом. До него удалить можно было только файлы, а затем уже пустую папку (которая собственно файл).

В том числе эта особенность лишила смысла применение fk. Нажав "удалить" я должен удалить потомка, проверить не сдохли ли все и только тогда удалять родителя. Если не сдохли - оставить родителя вживых дабы не плодить сирот. Я думал именно так работает упомянутое каскадное. Оказалось как "в винде 3.11"

@cygapb-007 · 16.10.2013, 00:24

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

Или я как обычно не понял)))

Про FK - там скорее контроль существования элемента перед записью ссылки на него (ссылочная целостность), нежели каскадные операции. Хотя деталей MySQL я не знаю)

выкинуть SELECT FROM не сработает в случае

MySQL
1
2
SELECT * FROM table1 JOIN table2 ON table1.id = table2.parent_id;
SELECT * FROM table3 JOIN table2 ON table3.parent_id = table2.id ;

@sqlnub · 16.10.2013, 19:15 **[ТС]**

Задача такая. Есть БД и модель которая нарисована в той самой _table_references. Подразумевается что ни в каком месте скрипта не придется строить запросы по месту. Аргументы (пост или гет) шарашат в "роутер" который зная об устройстве БД строит по ним запрос. В нормальных 1 к многим отношениях все работает, а многие-ко-многим выпали из матки. Потому что получается не один, а два запроса. А надо один. А получается 2... Ну вот, найти закономерность сам не смог, решил задать вопрос.

Вспомнил про этот "роутер". Пользуясь случаем чтобы не плодить темы по пустякам. У него такой вот запрос:

SQL
1
2
3
4
5
6
7
8
9
10
11
SELECT 
`tables`.`table_schema`,
`tables`.`TABLE_NAME`, 
`tables`.`TABLE_TYPE`, 
`tables`.`TABLE_ROWS`, 
`columns`.`COLUMN_NAME`, 
`columns`.`COLUMN_TYPE`, 
`columns`.`COLUMN_KEY`, 
`columns`.`EXTRA` 
FROM `information_schema`.`columns` INNER JOIN `information_schema`.`tables` USING(`table_name`) WHERE `columns`.`table_schema` = '_DB_NAME_' AND `tables`.`table_schema` = '_DB_NAME_' 
ORDER BY `tables`.`table_name` ASC

_DB_NAME_ - имя базы, выделил так чтобы видно было. Почему-то не получается присвоить за один раз:

SQL
1
WHERE `columns`.`table_schema` = `tables`.`table_schema` = '_DB_NAME_'

Ошибки нет, но результат явно недостаточный получается. Так нельзя делать? Надо обязательно через And?

@cygapb-007 · 17.10.2013, 09:41

в "роутер", ятд, должны передааться не 2 раза по 2 названия таблицы, а 1 раз 3 названия - тогда возможно сгенерировать все допустимые связи по трем таблицам. При этом формирование, имхо, проще разбить на 4 UNION, отслеживающих положение связывающей таблицы в полях _table_references, как я предложил ранее

Операция "=" имеет только 2 аргумента - левый и правый, так что без AND никак

Если брать в скобки, то результат сравнения 0 или 1, что, в свою очередь, будет сравниваться с оставшейся частью - не то, что бы хотелось

@sqlnub · 17.10.2013, 22:36 **[ТС]**

Оказалось все так и ест. Надо только местами поменять корень и реф. Получается соединение наоборот, но зато получается. Было

SQL
1
2
SELECT * FROM `Каталог` JOIN `Продукты_Каталога` ON `Каталог`.`Код_каталога` = `Продукты_Каталога`.`Код_каталога`;
SELECT * FROM `Продукт` JOIN `Продукты_Каталога` ON `Продукт`.`Код_продукта` = `Продукты_Каталога`.`Код_продукта`;

Надо

SQL
1
2
SELECT * FROM  `Продукты_Каталога` JOIN `Каталог` ON `Каталог`.`Код_каталога` = `Продукты_Каталога`.`Код_каталога`;
SELECT * FROM `Продукты_Каталога` JOIN `Продукт` ON `Продукт`.`Код_продукта` = `Продукты_Каталога`.`Код_продукта`;

Выкидываем силект фром и конец оператора:

SQL
1
2
SELECT * FROM  `Продукты_Каталога` JOIN `Каталог` ON `Каталог`.`Код_каталога` = `Продукты_Каталога`.`Код_каталога`
JOIN `Продукт` ON `Продукт`.`Код_продукта` = `Продукты_Каталога`.`Код_продукта`;

И оно показывает.

Интуиция не подвела. Теперь надо скрипт научить ее проявлять, то есть собирать из кучи силектов один.

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

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Работа со звуком через SDL3_mixer 8Observer8 08.02.2026 Содержание блога Пошагово создадим проект для загрузки звукового файла и воспроизведения звука с помощью библиотеки SDL3_mixer. Звук будет воспроизводиться по клику мышки по холсту на Desktop и по. . .	SDL3 для Web (WebAssembly): Основы отладки веб-приложений на SDL3 по USB и Wi-Fi, запущенных в браузере мобильных устройств 8Observer8 07.02.2026 Содержание блога Браузер Chrome имеет средства для отладки мобильных веб-приложений по USB. В этой пошаговой инструкции ограничимся работой с консолью. Вывод в консоль - это часть процесса. . .	SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .
SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .	SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	17.10.2013, 09:41
	в "роутер", ятд, должны передааться не 2 раза по 2 названия таблицы, а 1 раз 3 названия - тогда возможно сгенерировать все допустимые связи по трем таблицам. При этом формирование, имхо, проще разбить на 4 UNION, отслеживающих положение связывающей таблицы в полях _table_references, как я предложил ранее Операция "=" имеет только 2 аргумента - левый и правый, так что без AND никак Если брать в скобки, то результат сравнения 0 или 1, что, в свою очередь, будет сравниваться с оставшейся частью - не то, что бы хотелось 0