При разработке сложных отчетов или выборок в системе 1С программисты часто сталкиваются с задачей фильтрации данных «от противного». Например, нужно найти все долговые обязательства, по которым не было ни одного результативного контакта. Обычное внутреннее соединение с условием РезультативныйКонтакт = ЛОЖЬ в таких случаях работает некорректно: если по объекту есть два мероприятия (одно результативное, другое нет), объект все равно попадет в выборку из-за того самого «нерезультативного» мероприятия.
В этой статье мы проанализируем, почему стандартные фильтры могут выдавать лишние данные, и рассмотрим несколько надежных способов реализации строгой логики исключения в языке запросов 1С.
Рассмотрим ситуацию, описанную в теме. У разработчика есть таблица ДолговыеОбязательства и связанная таблица Задача.Мероприятие. Требуется выбрать только те обязательства, у которых все мероприятия имеют признак РезультативныйКонтакт = ЛОЖЬ.
Если мы напишем запрос следующим образом:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДолговыеОбязательства.Ссылка
ИЗ
Справочник.ДолговыеОбязательства КАК ДолговыеОбязательства
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Задача.Мероприятие КАК Мероприятие
ПО ДолговыеОбязательства.Ссылка = Мероприятие.Объект
ГДЕ
Мероприятие.Результат.РезультативныйКонтакт = ЛОЖЬ
Мы получим ошибку логики. Если по объекту "Долг №1" было создано Мероприятие А (результативное) и Мероприятие Б (нерезультативное), то условие ГДЕ найдет Мероприятие Б, соединит его с "Долгом №1" и выведет его в результат. Тот факт, что у этого же долга есть Мероприятие А, будет проигнорирован. В итоге в список попадут объекты, которые на самом деле имеют успешные контакты.
Это самый понятный и логичный способ. Сначала мы выберем все «плохие» объекты (те, у которых был хотя бы один результативный контакт), а затем исключим их из основного списка.
Рассмотрим по шагам, как оптимизировать и исправить исходный запрос:
В (...), чтобы избавиться от бесконечных ИЛИ.НЕ Ссылка В (...) для фильтрации объектов с результативными контактами.
ВЫБРАТЬ РАЗЛИЧНЫЕ ПЕРВЫЕ 1000
ДолговыеОбязательства.Ссылка КАК Ссылка
ИЗ
Справочник.ДолговыеОбязательства КАК ДолговыеОбязательства
ГДЕ
ДолговыеОбязательства.ДополнительныеРеквизиты.Значение.Наименование = "В работе"
И ДолговыеОбязательства.Должник.ЧасовойПояс В ("Antarctica/Vostok", "Antarctica/Mawson", "Asia/Baku", "Antarctica/Syowa", "EET")
И ДолговыеОбязательства.ЗапретАвтообзвона = ЛОЖЬ
И ДолговыеОбязательства.НеконтактныеНомера = ЛОЖЬ
И ДолговыеОбязательства.Ссылка В (ВЫБРАТЬ т.Объект ИЗ Задача.Мероприятие КАК т) // Должны быть мероприятия
И НЕ ДолговыеОбязательства.Ссылка В (
ВЫБРАТЬ
МероприятияИсключения.Объект
ИЗ
Задача.Мероприятие КАК МероприятияИсключения
ГДЕ
МероприятияИсключения.Результат.РезультативныйКонтакт = ИСТИНА
)
Проанализируем другой подход, предложенный в обсуждении. Мы можем соединить таблицы и сгруппировать результат по объекту долгового обязательства. Если мы возьмем МАКСИМУМ от булева поля РезультативныйКонтакт, то для объектов, имеющих хотя бы одно значение ИСТИНА, максимум будет равен ИСТИНА (так как в 1С Булево значение Истина > Ложь).
Реализуем это через конструкцию ИМЕЮЩИЕ:
ВЫБРАТЬ
ДолговыеОбязательства.Ссылка КАК Ссылка
ИЗ
Справочник.ДолговыеОбязательства КАК ДолговыеОбязательства
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Задача.Мероприятие КАК Мероприятие
ПО ДолговыеОбязательства.Ссылка = Мероприятие.Объект
ГДЕ
ДолговыеОбязательства.Должник.ЧасовойПояс В (&СписокПоясов)
И ДолговыеОбязательства.ЗапретАвтообзвона = ЛОЖЬ
СГРУППИРОВАТЬ ПО
ДолговыеОбязательства.Ссылка
ИМЕЮЩИЕ
МАКСИМУМ(ВЫБОР
КОГДА Мероприятие.Результат.РезультативныйКонтакт = ИСТИНА ТОГДА 1
ИНАЧЕ 0
КОНЕЦ) = 0
Здесь мы явно преобразуем Булево в число для надежности агрегации. Если сумма или максимум «успехов» равен 0, значит, объект нам подходит.
Для повышения производительности, особенно если данных много, рекомендуется разбить запрос на этапы. Это упростит работу планировщику СУБД.
Шаг 1: Отберем во временную таблицу все подходящие долговые обязательства по основным фильтрам (статус, часовой пояс).
ВЫБРАТЬ
ДолговыеОбязательства.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Обязательства
ИЗ
Справочник.ДолговыеОбязательства КАК ДолговыеОбязательства
ГДЕ
ДолговыеОбязательства.ЗапретАвтообзвона = ЛОЖЬ
И ДолговыеОбязательства.Должник.ЧасовойПояс В (&ТаймЗоны)
И ДолговыеОбязательства.ДополнительныеРеквизиты.Значение.Наименование = "В работе"
;
Шаг 2: Выберем объекты, которые имеют результативные контакты, в другую временную таблицу.
ВЫБРАТЬ РАЗЛИЧНЫЕ
Мероприятие.Объект КАК Объект
ПОМЕСТИТЬ ВТ_Результативные
ИЗ
Задача.Мероприятие КАК Мероприятие
ГДЕ
Мероприятие.Результат.РезультативныйКонтакт = ИСТИНА
;
Шаг 3: Получим финальный результат, используя левое соединение и проверку на NULL.
ВЫБРАТЬ
ВТ_Обязательства.Ссылка
ИЗ
ВТ_Обязательства КАК ВТ_Обязательства
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Результативные КАК ВТ_Результативные
ПО ВТ_Обязательства.Ссылка = ВТ_Результативные.Объект
ГДЕ
ВТ_Результативные.Объект ЕСТЬ NULL // Оставляем только те, которых нет в списке результативных
В процессе обсуждения на форуме были затронуты важные моменты, которые стоит учитывать каждому разработчику:
И и ИЛИ. В исходном сообщении отсутствие скобок приводило к тому, что условия накладывались хаотично. Оператор И выполняется первым, ИЛИ — вторым.Мероприятие.Результат.РезультативныйКонтакт создают неявные LEFT JOIN. Если таких обращений много, запрос становится тяжелым. Использование временных таблиц помогает сократить количество таких соединений.ЛЕВОЕ СОЕДИНЕНИЕ важно проверять именно ключевое поле (ссылку) на NULL. Условие ЕСТЬ NULL — это стандартный способ реализации исключения записей.NULL не только из-за отсутствия данных, но и из-за отсутствия прав у пользователя на чтение конкретного объекта.Проанализировав ситуацию, мы выяснили, что ключ к решению подобных задач — это переход от линейного условия ГДЕ к архитектуре запроса, использующей подзапросы или временные таблицы для четкого разделения «желаемых» и «нежелательных» данных.