При разработке интерфейсов в системе «1С:Предприятие» программисты часто сталкиваются с необходимостью вывода различных аналитических данных непосредственно в форму списка. Типичный пример — динамический список справочника Номенклатура, в котором помимо стандартных реквизитов (код, артикул, наименование) требуется отобразить максимальную цену товара из всех доступных видов цен, а также показать, к какому именно виду цен (оптовая, розничная, дилерская и т. д.) эта максимальная цена относится.
Казалось бы, задача тривиальная. Однако при попытке решить её «в лоб» через простое группировочное левое соединение разработчики сталкиваются с серьезной проблемой: динамический список начинает дублировать строки номенклатуры или выдавать ошибку неуникальности ключа записи. В этой статье мы подробно разберем, почему возникает эта ошибка, как правильно составить поисковый запрос для динамического списка и как решить задачу максимально эффективно с точки зрения производительности базы данных.
Для того чтобы получить максимальную цену по каждой позиции номенклатуры, нам достаточно использовать агрегатную функцию МАКСИМУМ(Цена) с группировкой по номенклатуре. Но как только мы пытаемся вытащить в этот же запрос поле ВидЦен (или ТипЦен), СУБД требует включить это поле в секцию СГРУППИРОВАТЬ ПО.
В результате группировка начинает отрабатывать по связке Номенклатура + Вид цены. Запрос возвращает не одну максимальную цену для товара, а максимальную цену для каждого вида цены этого товара. На экране пользователя появляется множество одинаковых строк номенклатуры, различающихся только ценами и их видами. В динамическом списке, где ключевым полем является ссылка на номенклатуру, это приводит к критическому сбою работы интерфейса.
Давайте разберем по шагам, как решить эту проблему с помощью языка запросов 1С и альтернативных механизмов платформы.
Проанализируем ситуацию и составим запрос, который соединяет справочник номенклатуры со специальным вложенным запросом. Идея заключается в том, чтобы разделить получение максимального значения цены и определение вида этой цены на два независимых этапа внутри запроса.
Рассмотрим пример готового запроса для динамического списка, основанный на финальном решении автора темы:
ВЫБРАТЬ
СправочникНоменклатура.Ссылка КАК Ссылка,
СправочникНоменклатура.Код КАК Код,
СправочникНоменклатура.Артикул КАК Артикул,
СправочникНоменклатура.Наименование КАК Наименование,
СправочникНоменклатура.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
СправочникНоменклатура.Производитель КАК Производитель,
СправочникНоменклатура.Родитель КАК Родитель,
ЕСТЬNULL(МаксЦены.Цена, 0) КАК Цена,
ЦеныНоменклатурыДетально.ВидЦен КАК ВидЦен
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
// Шаг 1. Получаем строго одну максимальную цену по каждой номенклатуре
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ЦеныСрез.Номенклатура КАК Номенклатура,
МАКСИМУМ(ЦеныСрез.Цена) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ) КАК ЦеныСрез
СГРУППИРОВАТЬ ПО
ЦеныСрез.Номенклатура
) КАК МаксЦены
ПО (МаксЦены.Номенклатура = СправочникНоменклатура.Ссылка)
// Шаг 2. Соединяем повторно с регистром цен для определения Вида цен
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ) КАК ЦеныНоменклатурыДетально
ПО (ЦеныНоменклатурыДетально.Номенклатура = МаксЦены.Номенклатура)
И (ЦеныНоменклатурыДетально.Цена = МаксЦены.Цена)
ГДЕ
СправочникНоменклатура.ЭтоГруппа = ЛОЖЬ
Выясним причину, почему этот запрос работает корректно в большинстве случаев. Во вложенном запросе МаксЦены мы группируем записи исключительно по полю Номенклатура и вычисляем максимум цены. На этом этапе мы гарантированно получаем одну строку на один товар. Затем мы выполняем левое соединение со вторым срезом последних ЦеныНоменклатурыДетально по двум условиям равенства — совпадению номенклатуры и совпадению цены. Таким образом, мы «подтягиваем» вид цены, который соответствует этой максимальной стоимости.
Несмотря на то что представленный выше запрос решает базовую задачу, в нем кроется логическая ловушка. Представим ситуацию: у товара «Ноутбук» установлены две цены разных видов, но с одинаковым максимальным значением. Например, «Оптовая цена» составляет 50 000 рублей, и «Розничная цена» тоже составляет 50 000 рублей. Обе эти цены являются максимальными для данного товара.
При выполнении левого соединения по условию ЦеныНоменклатурыДетально.Цена = МаксЦены.Цена СУБД найдет две подходящие записи в регистре цен. В результате в динамическом списке для одного ноутбука отобразятся две строки с одинаковой ссылкой, что мгновенно приведет к ошибке платформы «Ключ записи не уникален».
Чтобы застраховаться от этой ошибки и гарантировать получение ровно одной записи, нам необходимо принудительно свернуть дубли по видам цен с помощью дополнительной агрегации (например, выбрав минимальный или максимальный вид цены из совпавших). Рассмотрим усовершенствованный и безопасный вариант запроса:
ВЫБРАТЬ
СправочникНоменклатура.Ссылка КАК Ссылка,
СправочникНоменклатура.Код КАК Код,
СправочникНоменклатура.Артикул КАК Артикул,
СправочникНоменклатура.Наименование КАК Наименование,
ЕСТЬNULL(ИтоговыеМаксЦены.Цена, 0) КАК Цена,
ИтоговыеМаксЦены.ВидЦен КАК ВидЦен
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
МаксЦены.Номенклатура КАК Номенклатура,
МаксЦены.Цена КАК Цена,
МИНИМУМ(ВсеЦены.ВидЦен) КАК ВидЦен // Устраняем дублирование при совпадении цен
ИЗ
(ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ) КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура) КАК МаксЦены
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ) КАК ВсеЦены
ПО ВсеЦены.Номенклатура = МаксЦены.Номенклатура
И ВсеЦены.Цена = МаксЦены.Цена
СГРУППИРОВАТЬ ПО
МаксЦены.Номенклатура,
МаксЦены.Цена
) КАК ИтоговыеМаксЦены
ПО ИтоговыеМаксЦены.Номенклатура = СправочникНоменклатура.Ссылка
ГДЕ
СправочникНоменклатура.ЭтоГруппа = ЛОЖЬ
Использование конструкции МИНИМУМ(ВсеЦены.ВидЦен) гарантирует, что даже при полном совпадении максимальных цен разных видов система выберет только один вид цены (первый по алфавиту или коду ссылки) и динамический список отобразит ровно одну строку для каждой номенклатуры.
Если в вашей конфигурации используются разные валюты для разных видов цен (например, оптовая цена задана в USD, а розничная — в RUB), то расчет максимума простым сравнением числовых значений цен в регистре приведет к грубым ошибкам.
Например, цена в 100 USD по курсу 90 рублей составляет 9 000 рублей. Розничная цена составляет 8 000 рублей. Очевидно, что максимальной является оптовая цена (9 000 рублей). Однако без пересчета валют СУБД сравнит числа 100 и 8000, признает число 8000 максимумом и выведет на экран неверную информацию.
Для корректного сравнения мультивалютных цен пересчет в единую валюту (например, в валюту регламентированного или управленческого учета) необходимо выполнять внутри самого первого вложенного запроса до применения агрегатной функции МАКСИМУМ(). Только после приведения всех цен к единому денежному знаменателю можно корректно вычислить настоящую максимальную цену.
Хотя первый вариант с вложенными запросами полностью решает задачу на уровне логики, он имеет существенный недостаток — низкую производительность. Динамический список работает в реальном времени, выполняя постраничный вывод данных при прокрутке. Использование виртуальных таблиц СрезПоследних с группировками и многократными соединениями по всему регистру цен заставит СУБД выполнять тяжелые вычисления при каждом движении колесика мыши пользователем. На больших базах данных это неизбежно приведет к зависаниям формы списка.
Начиная с версии платформы 8.3.10, разработчикам доступен гораздо более изящный и быстрый инструмент — событие таблицы формы ПриПолученииДанныхНаСервере. Посмотрим, как реализовать задачу с его помощью.
Шаг 1. Настройка динамического списка
В самом запросе динамического списка мы убираем любые соединения с таблицами цен. Запрос должен выбирать только данные самой номенклатуры. Это обеспечит моментальное открытие формы. В таблицу формы на управляемой форме добавляем две новые колонки, не связанные с реквизитами базы данных: Цена (тип Число) и ВидЦены (тип СправочникСсылка.ВидыЦен).
Шаг 2. Написание программного кода в модуле формы
Для таблицы динамического списка создаем обработчик события ПриПолученииДанныхНаСервере. Этот метод вызывается платформой только для тех строк, которые в текущий момент времени видны на экране пользователя (обычно это не более 30–40 строк). Мы программно получим цены только для этих строк, что выполнится за доли секунды.
Проанализируем программный код реализации:
&НаСервереБезКонтекста
Процедура СписокПриПолученииДанныхНаСервере(ИмяЭлемента, Настройки, Строки)
// Получаем массив ссылок только тех товаров, которые сейчас видны на экране
МассивНоменклатуры = Новый Массив;
Для Каждого СтрокаСписка Из Строки Цикл
МассивНоменклатуры.Добавить(СтрокаСписка.Ключ);
КонецЦикла;
Если МассивНоменклатуры.Количество() = 0 Тогда
Возврат;
КонецЕсли;
// Пишем быстрый точечный запрос по конкретному массиву номенклатуры
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ИтоговыеМаксЦены.Номенклатура КАК Номенклатура,
| ИтоговыеМаксЦены.Цена КАК Цена,
| ИтоговыеМаксЦены.ВидЦен КАК ВидЦен
|ИЗ
| (ВЫБРАТЬ
| МаксЦены.Номенклатура КАК Номенклатура,
| МаксЦены.Цена КАК Цена,
| МИНИМУМ(ВсеЦены.ВидЦен) КАК ВидЦен
| ИЗ
| (ВЫБРАТЬ
| Цены.Номенклатура КАК Номенклатура,
| МАКСИМУМ(Цены.Цена) КАК Цена
| ИЗ
| РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, Номенклатура В (&МассивНоменклатуры)) КАК Цены
| СГРУППИРОВАТЬ ПО
| Цены.Номенклатура) КАК МаксЦены
|
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, Номенклатура В (&МассивНоменклатуры)) КАК ВсеЦены
| ПО ВсеЦены.Номенклатура = МаксЦены.Номенклатура
| И ВсеЦены.Цена = МаксЦены.Цена
| СГРУППИРОВАТЬ ПО
| МаксЦены.Номенклатура,
| МаксЦены.Цена) КАК ИтоговыеМаксЦены";
Запрос.УстановитьПараметр("МассивНоменклатуры", МассивНоменклатуры);
Запрос.УстановитьПараметр("Период", ТекущаяДата()); // или нужная дата среза
Выборка = Запрос.Выполнить().Выбрать();
// Заполняем виртуальные колонки в отображаемых строках
Пока Выборка.Следующий() Цикл
СтрокаДанных = Строки[Выборка.Номенклатура];
Если СтрокаДанных <> Неопределено Тогда
СтрокаДанных.Данные.Цена = Выборка.Цена;
СтрокаДанных.Данные.ВидЦены = Выборка.ВидЦен;
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Разберем по шагам преимущества использования обработчика ПриПолученииДанныхНаСервере:
Номенклатура В (&МассивНоменклатуры)). Вместо сканирования миллионов записей цен по всей базе данных система обрабатывает ровно 40 строк.Для получения максимальной цены и её вида в динамическом списке 1С вы можете использовать два основных подхода. Если база данных небольшая и требуется быстрая реализация без написания программного кода, вы можете настроить сложный вложенный запрос с левым соединением непосредственно в схеме динамического списка (Решение 1), обязательно предусмотрев группировку МИНИМУМ(ВидЦен) для исключения дублирования одинаковых цен.
Если же вы разрабатываете решение для высоконагруженной системы, где важна скорость отклика интерфейса и плавность прокрутки списков, настоятельно рекомендуется использовать механизм дополнительной подгрузки данных через обработчик события ПриПолученииДанныхНаСервере (Решение 2).