Оглавление
Выборка из таблицы, переданной в параметре
Пакет запросов. Использование промежуточных результатов
Последовательное уточнение результата, повторяющиеся выборки с обогащением
Сборка результатов, левое соединение подготовленных результатов
Объединение результатов, использование однородных по структуре выборок в запросе с объединением
Работа с объектной моделью запроса через "Схему запроса" многими воспринимается как альтернатива работы непосредственно с текстом запроса. Действительно, сложный динамический запрос уже давно научились собирать в виде подготовленных шаблонов текста. Неудобство текста проявляется в случаях, когда требуется обратиться к отдельным частям уже собранного запроса: полю выборки, условию отбора, параметру виртуальной таблицы и т.д. И здесь обычно используются специальные метки, по которым можно произвести поиск и замену изменяемой части текста запроса.
Основной недостаток работы с текстом - это отсутствие рефлексии. Алгоритмы построения текста запроса из-за этого становятся узкоспециализированными. Делать какие-либо универсальные расширения по работе с текстом запроса становится крайне затруднительно и, наверное, поэтому я еще не встречал таких решений.
Предлагаю рассмотреть вариант использования "Схемы запроса" не как альтернативы работы с текстом, а как объект, на базе которого можно создать свой собственный вариант DSL по работе с запросами. На примере такой реализации в объекте "Модель запроса" рассмотрены варианты использования символа * для конструирования запросов в сравнении с работой с текстом запроса.
Основная область раскрытия потенциала использования символа * это выборки подготовленных источников. Если структура источника один раз задана или известна, то при обращениях к ней можно использовать символ *. В следующих примерах показаны сценарии такого использования при конструировании запроса.
Обычно таблица, переданная в параметре, имеет определенную структуру. Выборку из такой таблицы можно описать так:
ВЫБРАТЬ *
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ &ТабличнаяЧасть КАК Таблица
Это вполне валидный текст запроса. Далее в запросах пакета можно ссылаться на эту таблицу и её поля. Фактически платформа не имея информации о структуре таблицы позволяет писать такие запросы. На практике такое описание может быть неудобно, так как нельзя будет воспользоваться конструктором запроса.
Конструктор запросов - это не единственное ограничение такого подхода. Схема запроса также не будет нормально работать с таким текстом. Необходимость поддержки рефлексии, а также валидности схемы требует полного описания данных. Для полноты такого описания также может потребоваться знать и тип выбираемых данных.
Вот так этот же запрос выглядит при использовании "Модели запроса":
Заказ = Документы._ДемоЗаказПокупателя.СоздатьДокумент();
МодельЗапроса = Общий.МодельЗапроса()
;// ЗАПРОС ПАКЕТА. ВТ_СЧЕТА_НА_ОПЛАТУ
МодельЗапроса.ЗапросПакета().Поместить("ВТ_СЧЕТА_НА_ОПЛАТУ")
.Выбрать()
.Источник(Заказ.СчетаНаОплату)
.Поле("*")
;
Здесь в описании источника неявно передается структура таблицы через ссылку на реальную таблицу. В качестве описания источника подойдет: таблица значений, табличная часть, данные формы коллекция, коллекция колонок, строковое перечисление колонок через запятую или массив.
На первый взгляд кода как будто стало даже больше. Однако здесь стоит учесть, что в случае с текстом запроса мы имеем дело с синтаксической конструкцией, а при использовании Модели запроса мы явно вызываем последовательность операторов. Последнее позволяет достичь эффекта своего языка программирования без необходимости анализа текста и разбора синтаксических конструкций.
Не всегда удается в одном запросе, без промежуточных результатов, получить все необходимые данные. Причины выделения запросов для промежуточных результатов могут быть:
- необходимость получения агрегированных данных
- декомпозиция сложного запроса
- переиспользование результатов
В общем случае можно выделить два шаблона построения пакета запросов:
- Последовательное уточнение и агрегация
- Сборка результатов
- Комбинация предыдущих двух
Рассмотрим пример. В нашей демо базе есть три таблицы: _ДемоКонтрагенты, _ДемоДоговорыКонтрагентов, _ДемоЗаказПокупателя. Предположим таблицу заказов нужно связать с контрагентами и договорами через левое соединение. Следующий текст запроса невозможен:
ВЫБРАТЬ
_ДемоКонтрагенты.Ссылка КАК Контрагент,
_ДемоДоговорыКонтрагентов.Ссылка КАК ДоговорКонтрагента,
_ДемоЗаказПокупателя.Ссылка КАК Реализация
ИЗ
Справочник._ДемоКонтрагенты КАК _ДемоКонтрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Справочник._ДемоДоговорыКонтрагентов КАК _ДемоДоговорыКонтрагентов
ПО _ДемоКонтрагенты.Ссылка = _ДемоДоговорыКонтрагентов.Владелец
ЛЕВОЕ СОЕДИНЕНИЕ Документ._ДемоЗаказПокупателя КАК _ДемоЗаказПокупателя
ПО _ДемоДоговорыКонтрагентов.Ссылка = _ДемоЗаказПокупателя.Договор
ЛЕВОЕ СОЕДИНЕНИЕ Документ._ДемоЗаказПокупателя КАК _ДемоЗаказПокупателя
ПО _ДемоКонтрагенты.Ссылка = _ДемоЗаказПокупателя.Контрагент
Если попытаться описать такую связь в конструкторе, то получим сообщение об ошибке 'Противоречивая связь "#2"'. Описать такое соединение одновременно с двумя источниками слева к одному справа в принципе можно, но похоже это будет на обходное решение. Такой запрос лучше декомпозировать и представить двумя последовательными запросами в одном пакете. В первом запросе получаем результат соединения таблиц контрагентов и договоров, а во втором уже полученный результат соединяем с таблицей заказов.
Вот так будет выглядеть модель запроса:
МодельЗапроса = Общий.МодельЗапроса()
;// ЗАПРОС ПАКЕТА. ВТ_СТОРОНЫ_ДОГОВОРА
МодельЗапроса.ЗапросПакета().Поместить("ВТ_СТОРОНЫ_ДОГОВОРА")
.Выбрать()
.Источник("Справочник._ДемоКонтрагенты")
.Источник("Справочник._ДемоДоговорыКонтрагентов")
.ЛевоеСоединение("_ДемоКонтрагенты", "_ДемоДоговорыКонтрагентов")
.Связь("Ссылка = Владелец")
.Поле("_ДемоКонтрагенты.Ссылка", "Контрагент")
.Поле("_ДемоДоговорыКонтрагентов.Ссылка", "Договор")
;// ЗАПРОС ПАКЕТА.
МодельЗапроса.ЗапросПакета()
.Выбрать()
.Источник("ВТ_СТОРОНЫ_ДОГОВОРА")
.Источник("Документ._ДемоЗаказПокупателя")
.ЛевоеСоединение("ВТ_СТОРОНЫ_ДОГОВОРА", "_ДемоЗаказПокупателя")
.Связь("Контрагент, Договор")
.Поле("ВТ_СТОРОНЫ_ДОГОВОРА.*")
.Поле("_ДемоЗаказПокупателя.Ссылка", "Заказ")
;
В целом шаблон Последовательность представляет собой цепочку запросов, где каждый последующий запрос выбирает результат предыдущего и добавляет новые поля.
Продолжим предыдущий пример, добавим агрегацию данных. Для этого последний результат поместим во временную таблицу ВТ_ЗАКАЗЫ и в следующем запросе посчитаем количество заказов по измерениям.
МодельЗапроса = Общий.МодельЗапроса()
;// ЗАПРОС ПАКЕТА. ВТ_СТОРОНЫ_ДОГОВОРА
МодельЗапроса.ЗапросПакета().Поместить("ВТ_СТОРОНЫ_ДОГОВОРА")
.Выбрать()
.Источник("Справочник._ДемоКонтрагенты")
.Источник("Справочник._ДемоДоговорыКонтрагентов")
.ЛевоеСоединение("_ДемоКонтрагенты", "_ДемоДоговорыКонтрагентов")
.Связь("Ссылка = Владелец")
.Поле("_ДемоКонтрагенты.Ссылка", "Контрагент")
.Поле("_ДемоДоговорыКонтрагентов.Ссылка", "Договор")
;// ЗАПРОС ПАКЕТА. ВТ_ЗАКАЗЫ
МодельЗапроса.ЗапросПакета().Поместить("ВТ_ЗАКАЗЫ")
.Выбрать()
.Источник("ВТ_СТОРОНЫ_ДОГОВОРА")
.Источник("Документ._ДемоЗаказПокупателя")
.ЛевоеСоединение("ВТ_СТОРОНЫ_ДОГОВОРА", "_ДемоЗаказПокупателя")
.Связь("Контрагент, Договор")
.Поле("ВТ_СТОРОНЫ_ДОГОВОРА.*")
.Поле("_ДемоЗаказПокупателя.Ссылка", "Заказ")
;// ЗАПРОС ПАКЕТА.
МодельЗапроса.ЗапросПакета()
.Выбрать()
.Источник("ВТ_ЗАКАЗЫ")
.Количество("Заказ")
.Поле("*")
;
В этом примере используется агрегатная функция Количество(), а группировочные поля добавлены как все остальные через *. Указание отдельно группировочных полей здесь избыточно и потому опущено.
В этом примере агрегация применена к результату предыдущего запроса. Конечно вполне возможно было применить агрегацию и сразу, но здесь я имел в виду, что результат выборки и агрегации равнозначны для дальнейшего использования, например при сборке (см. дальше).
Этот вариант является модификацией шаблона последовательного уточнения. Отличие здесь в том, что вначале определяется набор измерений, а затем по этим измерениям путем обогащения слева описываются результаты. Окончательная сборка здесь происходит в конце, когда все необходимые результаты получены и соединены по набору измерений.
В следующем примере нам нужно получить статистические данные: количество документов по организации и контрагенту. Общий результат можно будет собрать в разрезе организации и контрагента: остатки взаиморасчетов и статистические данные.
Здесь явно можно выделить измерения: Организация и Контрагент. Таблица измерений затем может быть переиспользована для получения промежуточных результатов. Общий результат будет собран по измерениям путем левого соединения с промежуточными результатами.
Таким образом для применения шаблона Сборка необходимо: выделить измерения, получить промежуточные результаты, собрать общий результат.
И модель:
МодельЗапроса = Общий.МодельЗапроса()
;// ЗАПРОС ПАКЕТА. ВТ_ИЗМЕРЕНИЯ
МодельЗапроса.ЗапросПакета().Поместить("ВТ_ИЗМЕРЕНИЯ")
.Выбрать(, Истина)
.Источник("Документ.ЗаказПокупателя")
.Поле("ЗаказПокупателя.Организация")
.Поле("ЗаказПокупателя.Контрагент")
;// ЗАПРОС ПАКЕТА. ВТ_ПРОДАЖИ
МодельЗапроса.ЗапросПакета().Поместить("ВТ_ПРОДАЖИ")
.Выбрать()
.Источник("РегистрНакопления.Продажи.Обороты", "ПродажиОбороты")
.УсловиеВхождения("ВТ_ИЗМЕРЕНИЯ")
.Поле("ПродажиОбороты.Организация")
.Поле("ПродажиОбороты.Контрагент")
.Поле("ПродажиОбороты.СуммаОборот")
;// ЗАПРОС ПАКЕТА. ВТ_ДОЛГИ
МодельЗапроса.ЗапросПакета().Поместить("ВТ_ДОЛГИ")
.Выбрать()
.Источник("РегистрНакопления.РасчетыСПокупателями.Остатки", "РасчетыСПокупателямиОстатки")
.УсловиеВхождения("ВТ_ИЗМЕРЕНИЯ")
.Поле("РасчетыСПокупателямиОстатки.Организация")
.Поле("РасчетыСПокупателямиОстатки.Контрагент")
.Поле("РасчетыСПокупателямиОстатки.СуммаОстаток", "Долг")
;// ЗАПРОС ПАКЕТА.
МодельЗапроса.ЗапросПакета()
.Выбрать()
.Источник("ВТ_ИЗМЕРЕНИЯ")
.Источник("ВТ_ПРОДАЖИ")
.Источник("ВТ_ДОЛГИ")
.ЛевоеСоединение("ВТ_ИЗМЕРЕНИЯ", "ВТ_ПРОДАЖИ")
.Связь("Организация, Контрагент")
.ЛевоеСоединение("ВТ_ИЗМЕРЕНИЯ", "ВТ_ДОЛГИ")
.Связь("Организация, Контрагент")
.Поле("ВТ_ИЗМЕРЕНИЯ.*")
.Поле("ВТ_ПРОДАЖИ.*")
.Поле("ВТ_ДОЛГИ.*")
;
В модели обратите внимание, что сборка осуществляется по всем полям с использованием *. При добавлении полей через * в тексте запроса платформа для одинаковых псевдонимов полей сгенерирует дубли полей с псевдонимами с индексом, однако в модели такой прием позволяет оставлять первое поле с данным псевдонимом и пропускать все остальные с таким же псевдонимом. В "Модели запроса" работает также правило и для выражений полей: если поле уже есть, то при использовании * дубли добавлены не будут.
В шаблоне "Сборка результатов" создается таблица измерений. Предполагается, что эта таблица будет переиспользована в запросах получения промежуточных результатов в правом соединении. Однако в случае получения данных из виртуальных таблиц эта таблица может быть использована в параметрах таблицы через условие вхождения.
Обычно условие вхождения имеет вид:
(Организация, Контрагент) В
(ВЫБРАТЬ
ВТ_ИЗМЕРЕНИЯ.Организация,
ВТ_ИЗМЕРЕНИЯ.Контрагент
ИЗ
ВТ_ИЗМЕРЕНИЯ КАК ВТ_ИЗМЕРЕНИЯ)
С использованием символа * это же условие могло бы выглядеть так:
(Организация, Контрагент) В
(ВЫБРАТЬ
*
ИЗ
ВТ_ИЗМЕРЕНИЯ КАК ВТ_ИЗМЕРЕНИЯ)
И при использовании Модели запроса, так:
УсловиеВхождения("ВТ_ИЗМЕРЕНИЯ")
Промежуточные возможные варианты:
- УсловиеВхождения("ВТ_ИЗМЕРЕНИЯ", “Организация = Организация, Контрагент = Контрагент”)
- УсловиеВхождения("ВТ_ИЗМЕРЕНИЯ", “Организация, Контрагент”)
- УсловиеВхождения("ВТ_ИЗМЕРЕНИЯ", “*”)
* аналогичное условие можно сделать и для соединения: СвязьВхождения(“ВТ_ИЗМЕРЕНИЯ”).
При условии использования определенных ограничений шаблона можно добиться значительного сокращения объема кодирования запроса. В данном шаблоне необходимо, чтобы таблица измерений полностью подходила по составу полей. Различия в наименованиях здесь не будут критичны, однако при их наличиях потребуется дополнительно описать соответствие полей вхождения. Последнее это уже вопрос архитектуры данных. Лучший вариант архитектуры - когда наименования одинаковых сущностей в разных системах одинаковы.
Другой вариант сборки - это объединение. Объединение является лучшим вариантом, когда набор ключевых данных нельзя получить из одного источника.
Объединение также подразумевает однородную структуру объединяемых данных. Это важное обстоятельство позволяет исходить из того, что ключевые поля будут одинаковые, а различия могут быть в ресурсных полях и в источнике. Тогда первый оператор выборки можно взять за образец и последующие получить копированием из первого. После копирования достаточно поменять источник и при необходимости ресурсные поля.
Еще одни вариант устранения избыточности - использовать в каждом объединении символ *. Однако такой вариант подходит только в случае полного совпадения структуры полей для выбираемых источников объединения.
И модель:
МодельЗапроса = Общий.МодельЗапроса()
;// ЗАПРОС ПАКЕТА. ВТ_РАБОТА_С_ПОКУПАТЕЛЯМИ
МодельЗапроса.ЗапросПакета().Поместить("ВТ_РАБОТА_С_ПОКУПАТЕЛЯМИ")
.Выбрать()
.Источник("РегистрНакопления._ДемоОборотыПоСчетамНаОплату.Обороты")
.Поле("Номенклатура")
.Поле("СуммаОборот")
.Поле("0", "КоличествоОборот")
.ОбъединитьВсе(,, 0)
.ЗаменитьИсточник("Обороты", "РегистрНакопления._ДемоОстаткиТоваровВМестахХранения.Обороты")
.Поле("0", "СуммаОборот")
.Поле("КоличествоОборот", "КоличествоОборот")
;// ЗАПРОС ПАКЕТА. ВТ_РАБОТА_С_ПОКУПАТЕЛЯМИ
МодельЗапроса.ЗапросПакета()
.Выбрать()
.Источник("ВТ_РАБОТА_С_ПОКУПАТЕЛЯМИ")
.Сумма("СуммаОборот")
.Сумма("КоличествоОборот")
.Поле("*")
;
Здесь использован оператор ОбъединитьВсе, где указан индекс оператора для копирования. Копирование существующего оператора позволяет быстро строить большие объединения по подобию.
Удаление лишнего
Иногда источники данных могут содержать неиспользуемые поля. В таком случае при добавлении полей через * часть полей необходимо будет удалить. Конечно, здесь можно поступить и по-другому - просто перечислить необходимые поля. В таком случае мы лишимся возможности подстраиваться нашему запросу при будущих изменениях.
Пропагандируя использование символа * в запросах я исхожу из того, что выборка производится для целей формирования движений. В таком случае данные как правило выбираются полностью по всему набору доступных полей источника. Использование * как раз удовлетворяет этому условию и позволяет адаптироваться выборке под будущие изменения в структуре данных.
В тех случаях, когда структура данных не полностью удовлетворяет данному критерию, можно использовать операторы УдалитьПоле, УдалитьКолонку - первый на уровне оператора выборки, второй для запроса пакета. Или же отказаться от использования символа и перечислить необходимые поля запроса напрямую.
В рассмотренных примерах показаны примеры использования объектной модели, в которой на свойстве рефлексии стало возможным реализовать расширенное поведение при использовании символа * для конструирования текста запроса.
В принципе в объектной модели можно реализовать свои собственные расширения по работе со схемой запроса. Простор для реализации может быть потенциально большим: работа с СКД, работа с правами RLS, работа с характеристиками и многое другое. Надеюсь представленные примеры смогли вас в этом убедить.
Еще на примере использования * в модели запроса я хотел показать, что схема запроса - это не просто другой вариант работы с текстом запроса, а это в принципе заготовка для построения своего собственного DSL по работе с запросом с минимальными затратами.
