При разработке произвольных запросов для динамических списков разработчики часто сталкиваются с ошибкой: «Обнаружено дублирование ключевых значений в колонках...» с перечислением всех полей выборки. Разберем причины возникновения этой ошибки, механику работы динамического списка и правильные паттерны построения запросов для фильтрации и сравнения данных.
Проанализируем механику работы динамического списка платформы «1С:Предприятие». В отличие от обычной таблицы значений, динамический список считывает данные порциями и требует строго уникальной идентификации каждой строки:
ВЫБРАТЬ. Если в результате выполнения запроса появляются две и более строки с идентичным набором значений, генерируется исключение дублирования ключей.Выясним причину, почему дубли появились в исходном запросе автора:
ВЫБРАТЬ
ПериодическийПриход.Период КАК Период,
ПериодическийПриход.Кошелек КАК Кошелек,
ПериодическийПриход.Отправитель КАК Отправитель,
ПериодическийПриход.Сумма КАК Сумма,
ПериодическийПриход.УИД КАК УИД
ИЗ
РегистрСведений.ПериодическийПриход.СрезПоследних КАК ПериодическийПриход
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.РегистрПоступления КАК РегистрПоступления
ПО ПериодическийПриход.УИД <> РегистрПоступления.УИД
Посмотрим на условие связи ПО Таблица1.УИД <> Таблица2.УИД. Такое условие соединения приводит к соединению каждой строки первой таблицы со всеми строками второй таблицы, у которых идентификатор отличается. В результате возникает эффект декартова произведения: одна и та же строка исходного регистра сведений начинает повторяться столько раз, сколько несовпадающих записей найдено во второй таблице.
Разберем классический шаблон языка запросов 1С для поиска записей, которых нет во второй таблице. Вместо внутреннего соединения по условию <> применим левое соединение по равенству ключей с последующей проверкой на NULL в секции ГДЕ.
Посмотрим на пример корректного запроса:
ВЫБРАТЬ
ПериодическийПриход.Период КАК Период,
ПериодическийПриход.Регистратор КАК Регистратор,
ПериодическийПриход.НомерСтроки КАК НомерСтроки,
ПериодическийПриход.Активность КАК Активность,
ПериодическийПриход.Кошелек КАК Кошелек,
ПериодическийПриход.Отправитель КАК Отправитель,
ПериодическийПриход.Сумма КАК Сумма,
ПериодическийПриход.УИД КАК УИД
ИЗ
РегистрСведений.ПериодическийПриход.СрезПоследних(&ПериодСреза, ) КАК ПериодическийПриход
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПериодическиеПроведенные.СрезПоследних(&ПериодСреза, ) КАК ПериодическиеПроведенные
ПО ПериодическийПриход.УИД = ПериодическиеПроведенные.УИД
ГДЕ
ПериодическиеПроведенные.УИД ЕСТЬ NULL
При таком подходе для каждой записи таблицы ПериодическийПриход ищется точное совпадение в таблице ПериодическиеПроведенные. Если совпадений нет, правая часть заполняется значениями NULL, а условие ПериодическиеПроведенные.УИД ЕСТЬ NULL оставляет в выборке только не проведенные строки без возникновения дубликатов.
Рассмотрим частую задачу: запланированные операции должны отображаться каждый месяц в определенный день до тех пор, пока не будут фактически проведены в текущем месяце. Если факт уже зафиксирован в этом месяце, запись должна скрываться, но в следующем месяце появляться снова.
Проанализируем распространенные ошибки при работе с датами:
ДЕНЬ(Поле) = &День и МЕСЯЦ(Поле) <= &Месяц лишают СУБД возможности использовать индексы (нарушают SARGability), приводя к полному сканированию таблиц (Table Scan).МЕСЯЦ(Дата1) <= МЕСЯЦ(Дата2) перестает работать при переходе через границу года (например, при сравнении января с декабрем предыдущего года).Для корректной работы периодического механизма свяжем факт проведения не только по идентификатору, но и по интервалу дат текущего периода.
Разберем пример запроса для динамического списка с фильтрацией по месяцу:
ВЫБРАТЬ
План.Период КАК Период,
План.Кошелек КАК Кошелек,
План.Отправитель КАК Отправитель,
План.Сумма КАК Сумма,
План.УИД КАК УИД
ИЗ
РегистрСведений.ПериодическийПриход.СрезПоследних(&ТекущаяДата, ) КАК План
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПериодическиеПроведенные КАК Факт
ПО План.УИД = Факт.УИД
И Факт.Период МЕЖДУ &НачалоМесяца И &КонецМесяца
ГДЕ
Факт.УИД ЕСТЬ NULL
Альтернативный вариант построения фильтра без левого соединения — использование подзапроса с конструкцией НЕ В:
ВЫБРАТЬ
План.Период КАК Период,
План.Кошелек КАК Кошелек,
План.Отправитель КАК Отправитель,
План.Сумма КАК Сумма,
План.УИД КАК УИД
ИЗ
РегистрСведений.ПериодическийПриход.СрезПоследних(&ТекущаяДата, ) КАК План
ГДЕ
План.УИД НЕ В (
ВЫБРАТЬ
Факт.УИД
ИЗ
РегистрСведений.ПериодическиеПроведенные КАК Факт
ГДЕ
Факт.Период МЕЖДУ &НачалоМесяца И &КонецМесяца
)
Рассмотрим, как правильно инициализировать параметры периода в модуле управляемой формы перед открытием или при изменении даты. Вычисление границ дат через встроенный язык работает быстрее и надежнее:
&НаСервере
Процедура УстановитьПараметрыСпискаСервер(ВыбраннаяДата)
Список.Параметры.УстановитьЗначениеПараметра("ТекущаяДата", ВыбраннаяДата);
Список.Параметры.УстановитьЗначениеПараметра("НачалоМесяца", НачалоМесяца(ВыбраннаяДата));
Список.Параметры.УстановитьЗначениеПараметра("КонецМесяца", КонецМесяца(ВыбраннаяДата));
КонецПроцедуры
Для предотвращения ошибок в динамических списках со сложными выборками придерживаемся следующих правил:
Основная таблица, следим, чтобы в запросе не использовались соединения «один-ко-многим», размножающие строки основной таблицы.СрезПоследних всегда передаем параметр даты среза &ПериодСреза, иначе срез будет рассчитываться на текущую максимальную дату в базе данных.