Схемы представления и типы соединений
Когда Вы строите представление (view) в системе — по сути Вы собираете одну «плоскую» таблицу из нескольких источников. У каждого источника свои поля, свои связи, и система должна понимать: как именно соединять таблицы между собой, чтобы получить корректный набор строк.
Это и есть тип соединения (join).
Схема представления — это визуальная карта того, как связаны таблицы:
-
какие сущности являются главными,
-
какие таблицы зависят от других,
-
как именно между ними устанавливается связь.
В итоге Вы получаете один финальный датасет, который можно использовать в отчете, таблице, дашборде или Stimulsoft-отчете.
Почему это важно
Без корректной схемы:
-
строки многократно дублируются;
-
пропадают данные, которые должны быть;
-
соединения возвращают лишнее;
-
отчеты начинают показывать «кашу».
Тип соединения определяет, какие строки попадут в финальную таблицу, какие — исчезнут, а какие — дублируются.
Основные типы соединений
Разберем только то, что реально используется в LIMS/Modus BI:
1. INNER JOIN — строгий «пересечение»
Показываются только те строки, у которых есть совпадение по ключу в обеих таблицах.
Пример.
Таблица Сотрудник:
| id | ФИО |
|---|---|
| 1 | Иванов |
| 2 | Петров |
| 3 | Сидоров |
Таблица ДолжностиСотрудника:
| id | сотрудник_id | должность |
|---|---|---|
| 1 | 1 | Химик |
| 2 | 2 | Метролог |
INNER JOIN вернет:
| ФИО | Должность |
|---|---|
| Иванов | Химик |
| Петров | Метролог |
Сидоров пропадет, т. к. у него нет записи в связанной таблице.
Использовать, когда нужны только «полные» данные.
2. LEFT JOIN — сохранить все записи из левой таблицы
Вы берете таблицу А, присоединяете таблицу Б — и получаете все строки А всегда, даже если в Б нет соответствия.
Пример:
Используем те же данные.
LEFT JOIN (Сотрудник ← ДолжностиСотрудника):
| ФИО | Должность |
|---|---|
| Иванов | Химик |
| Петров | Метролог |
| Сидоров | NULL |
Сидоров остался — просто без должности.
LEFT JOIN — основа для справочников, вложенных списков, множественных критериев, полномочий и всего, где связь необязательная.
3. RIGHT JOIN — зеркальный LEFT JOIN
Почти не используется в моделировании приложений.
Проще всегда строить модель так, чтобы левая таблица была основной.
4. EXISTS — проверить, что связанные элементы существуют
Это не соединение в классическом смысле.
Это фильтр: оставить только те строки, у которых есть записи в другой таблице.
Пример:
Оставить только сотрудников, у которых есть хотя бы одно полномочие.
5. WHERE CONDITION — соединение через условие
Редкий случай.
Это когда связь не по полю id, а по выражению.
Например:
employee.age > 40 AND department.name = 'Лаборатория'
Да, вот итоговая таблица в расширенном виде — с пояснением, какие типы связей между таблицами обычно стоят за каждым видом соединения и какие ошибки или особенности при этом бывают.
Сводная таблица по видам соединений и типам связей
| Тип JOIN / фильтра | Смысл операции | Тип связи (в моделях данных) | Что возвращает | Когда применять | Возможные проблемы / особенности |
|---|---|---|---|---|---|
| INNER JOIN | Пересечение: взять только строки, у которых есть совпадения в обеих таблицах | Обычно 1:1 или 1:М, где обе стороны обязательны (например, сотрудник ↔ должность, если каждый сотрудник имеет должность) | Только совпадающие записи | Когда нужны только «полные» данные: отчеты, статистика, фильтры с обязательными атрибутами | Если в подчинённой таблице несколько строк на один ключ — получаются дубликаты. Нужно агрегировать (GROUP BY или DISTINCT). |
| LEFT JOIN | Сохранить все строки из левой таблицы, даже если справа нет соответствия | 1:М или 1:0..1, где правая часть необязательна (например, сотрудник → отдел, если у некоторых нет отдела) | Все строки из левой таблицы, с NULL вместо отсутствующих данных справа | При работе со справочниками, данными, где могут быть пропуски или несвязанные элементы | При множественных совпадениях справа можно получить «раздутие» данных. Иногда решается подзапросом с агрегацией. |
| RIGHT JOIN | То же самое, что LEFT JOIN, но наоборот (все строки из правой таблицы) | Зеркально М:1 | Все строки из правой таблицы | Когда нужно сохранить полный список «справочника», даже если к нему никто не относится (редко используется) | Обычно заменяется на LEFT JOIN — проще читать и поддерживать. |
| FULL OUTER JOIN | Объединение всех записей из обеих таблиц | Используется при 1:М или М:М, если нужно показать всё, включая несвязанные элементы с обеих сторон | Все строки из обеих таблиц (и совпавшие, и нет) | Для аналитических отчетов, где нужно объединить все данные и отметить отсутствие связей | Не поддерживается во всех СУБД (в MySQL нет). Альтернатива — UNION двух LEFT/RIGHT JOIN. |
| EXISTS / NOT EXISTS | Проверка наличия связанных записей (без фактического объединения данных) | Используется при 1:М или М:М, когда важен факт существования | Только строки, удовлетворяющие условию | Для фильтров вида «покажи тех, у кого есть ...» или «нет ...» | Быстрее обычных JOIN при проверке наличия, но не возвращает связанные данные. |
| WHERE CONDITION (условное соединение) | Связь не по ключу, а по логическому условию (диапазоны, выражения, даты) | Гибкая связь, часто М:М или временные связи | Только строки, удовлетворяющие выражению | Для временных интервалов, диапазонов, текстовых совпадений | Легко получить лишние строки при неточном условии. Нет оптимизации индексов. |
Примеры типичных связей
| Связь | Пример таблиц | Тип JOIN обычно |
|---|---|---|
| 1:1 | Сотрудник ↔ ПаспортныеДанные | INNER JOIN |
| 1:0..1 | Сотрудник ↔ Должность (если не у всех есть) | LEFT JOIN |
| 1:М | Отдел ↔ Сотрудник | LEFT JOIN или INNER JOIN |
| М:М | Сотрудник ↔ Полномочия (через промежуточную таблицу) | INNER JOIN + промежуточная таблица |
| Проверка существования | Сотрудник, у которого есть запись в Полномочия | EXISTS |
| Логическая связь | Сотрудник, возраст которого больше 40 и отдел содержит слово «Лаборатория» | WHERE с условием |
Визуально — как это работает
| JOIN | Что происходит визуально |
|---|---|
| INNER JOIN | Пересечение двух множеств — только общие элементы |
| LEFT JOIN | Всё из левого множества + совпадения справа |
| RIGHT JOIN | Всё из правого множества + совпадения слева |
| FULL OUTER JOIN | Всё из обеих сторон |
| EXISTS | Проверка, что справа существует хотя бы одна строка |
| WHERE CONDITION | Гибкий фильтр по выражению, не обязательно по ключу |
Практическое использование в проектах
-
INNER JOIN — для аналитики, где важно совпадение данных (например, считать только сотрудников с активными должностями).
-
LEFT JOIN — для дашбордов, где нужна полная картина, включая пустые значения (например, список всех сотрудников, даже без полномочий).
-
EXISTS — для проверки логических условий (например, кто прошёл хотя бы один курс).
-
WHERE CONDITION — для гибких фильтров (например, «старше 40 лет и работают в лабораториях»).
Сложный пример
1. Логика сценария в CRM / ЛИМС-системе
-
Руководитель заранее создаёт:
-
справочник Полномочия (например, «Работа с химреактивами»);
-
справочник Требования к каждому полномочию («Прошел инструктаж», «Имеет допуск»);
-
справочник Критерии для каждого требования («Есть сертификат», «Есть отметка в журнале»).
-
-
Сотрудник получает назначение — руководитель добавляет к нему полномочие.
-
После назначения в карточке сотрудника должна появляться структура:
-
Полномочие
↳ Требования
↳ Критерии (для этого требования)
-
-
При раскрытии элемента сотрудника система показывает только те требования и критерии, которые реально относятся к его полномочиям (не глобальный справочник, а персонализированный фильтр).
2. Таблицы
| Таблица | Описание |
|---|---|
Сотрудник |
id, ФИО |
Полномочие |
id, название |
Требование |
id, полномочие_id, название |
Критерий |
id, требование_id, название |
Сотрудник_Полномочие |
сотрудник_id, полномочие_id |
Результат_Проверки |
сотрудник_id, критерий_id, статус |
3. Цель запроса
Показать для конкретного сотрудника (например, Иванова):
-
только его полномочия,
-
только требования, относящиеся к этим полномочиям,
-
только критерии, относящиеся к этим требованиям,
-
и статус выполнения каждого критерия именно этим сотрудником.
4. Запрос
SELECT
p.название AS Полномочие,
t.название AS Требование,
c.название AS Критерий,
COALESCE(r.статус, 'Не проверен') AS Статус
FROM Сотрудник s
INNER JOIN Сотрудник_Полномочие sp
ON s.id = sp.сотрудник_id
INNER JOIN Полномочие p
ON sp.полномочие_id = p.id
INNER JOIN Требование t
ON t.полномочие_id = p.id
INNER JOIN Критерий c
ON c.требование_id = t.id
LEFT JOIN Результат_Проверки r
ON r.критерий_id = c.id
AND r.сотрудник_id = s.id
WHERE s.id = 1 -- Иванов
ORDER BY p.название, t.название, c.название;
5. Почему именно так
| Этап | JOIN | Почему выбран | Что обеспечивает |
|---|---|---|---|
Сотрудник → Сотрудник_Полномочие |
INNER JOIN | Смотрим только тех, у кого действительно есть назначенные полномочия | Отсекает несвязанных сотрудников |
Сотрудник_Полномочие → Полномочие |
INNER JOIN | У каждого назначения должно быть конкретное полномочие | Показывает только актуальные полномочия |
Полномочие → Требование |
INNER JOIN | Требования обязательны для данного полномочия | Формирует разворот требований |
Требование → Критерий |
INNER JOIN | Без критериев требование не отображается | Дает детализацию |
Критерий → Результат_Проверки |
LEFT JOIN | Критерий может быть ещё не проверен, но должен отображаться | Сохраняет полную структуру даже без статуса |
WHERE s.id = ... — гарантирует, что всё дерево строится только для выбранного сотрудника.
6. Пример данных
Сотрудник
| id | ФИО |
|---|---|
| 1 | Иванов |
Полномочие
| id | название |
|---|---|
| 10 | Работа с химреактивами |
| 11 | Контроль приборов |
Сотрудник_Полномочие
| сотрудник_id | полномочие_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
Требование
| id | полномочие_id | название |
|---|---|---|
| 100 | 10 | Прошел инструктаж |
| 101 | 10 | Имеет допуск |
| 102 | 11 | Обслуживает приборы |
Критерий
| id | требование_id | название |
|---|---|---|
| 200 | 100 | Сертификат обучения |
| 201 | 100 | Проверка знаний |
| 202 | 101 | Отметка в журнале |
| 203 | 102 | Практическая проверка |
Результат_Проверки
| сотрудник_id | критерий_id | статус |
|---|---|---|
| 1 | 200 | выполнен |
| 1 | 201 | выполнен |
| 1 | 203 | невыполнен |
7. Результат для Иванова
| Полномочие | Требование | Критерий | Статус |
|---|---|---|---|
| Работа с химреактивами | Прошел инструктаж | Сертификат обучения | выполнен |
| Работа с химреактивами | Прошел инструктаж | Проверка знаний | выполнен |
| Работа с химреактивами | Имеет допуск | Отметка в журнале | Не проверен |
| Контроль приборов | Обслуживает приборы | Практическая проверка | невыполнен |
8. Логика в интерфейсе карточки
Именно так строится дерево данных при открытии сотрудника:
[Иванов]
├─ Работа с химреактивами
│ ├─ Прошел инструктаж
│ │ ├─ Сертификат обучения – выполнен
│ │ └─ Проверка знаний – выполнен
│ └─ Имеет допуск
│ └─ Отметка в журнале – Не проверен
└─ Контроль приборов
└─ Обслуживает приборы
└─ Практическая проверка – невыполнен
9. Почему это корректно с точки зрения связей
| Таблица | Роль в структуре | Связь | JOIN |
|---|---|---|---|
Сотрудник |
Основная сущность | 1:М к Сотрудник_Полномочие |
INNER |
Сотрудник_Полномочие |
Промежуточная таблица | М:1 к Полномочие |
INNER |
Полномочие |
Категория | 1:М к Требование |
INNER |
Требование |
Подэлемент | 1:М к Критерий |
INNER |
Критерий |
Проверяемый пункт | 1:М к Результат_Проверки (через сотрудника) |
LEFT |
10. Итого
-
INNER JOIN используется для всех уровней, которые гарантированно должны быть связаны с сотрудником.
-
LEFT JOIN применяется только к
Результат_Проверки, чтобы показать даже неоценённые критерии. -
WHERE s.id = ? — фильтрует дерево под конкретного сотрудника.
-
Таким образом, в карточке сотрудника отображается только его персональная структура полномочий, требований и критериев с актуальным статусом выполнения.
No comments to display
No comments to display