Примеры запросов для работы с иерархическими справочниками. Уровни, глубина, прародители, циклы и аналоги запросом Иерархия групп и элементов

Ildarovich 6489 16.11.12 18:24 Сейчас в теме

() Владимир! Рад, что обратили внимание на статью, тем более именно Вы и были одним из первых, кто увидел (и оценил) этот прием в обсуждении двухлетней давности "Реально написать хитрый запрос". Интересный вопрос я придумал не сам себе, а увидел его в форуме. Автор вопроса - Шепталов Станислав. Далее - 24.10.12 тот же (только что обратил на это внимание, так как никнэйм разный) участник форума задал похожий вопрос, но уже в применении к иерархии. Получается, что решен ПРАКТИЧЕСКИЙ вопрос. Далее, в соответствии с "научным" подходом я просмотрел практические задачи, где может применяться этот прием. Нашел еще 7 задач. 5 - в этой статье. Среди них задачу про циклы в спецификациях, которую я ранее обещал решить Ish_2 одним запросом. Думаю, Ish_2 сможет убедить Вас в актуальности этой задачи - он потратил на нее немало времени. Решение короткое - из нескольких строк, поэтому предельно понятное, сформулированное в непроцедурном стиле, через требование к результату. Ну и другие задачи встречались в статьях и на форуме, для них предлагались более громоздкие решения. Поэтому давайте подождем некоторое время, чтобы понять, как часто это будет применяться. Именно такой обратной связи я жду - от тех, кто будет пробовать.
Кстати, о том, что эта ветвь математики недалека от практики и нужна бухгалтерам свидетельствует общий модуль "КорректировкаСтоимости" в БП2, с которым как раз сейчас возимся (нестабильная работа штатного запроса). Там идет речь о разрыве циклов графа перемещений номенклатуры и построении остовного дерева.
Теперь о структуре базы данных «под конкретную задачу». Вопрос был задан про реализацию задачи на 1С и, поэтому, задача была решена на 1С. Если бы Вас спросили «на каком автобусе можно доехать до библиотеки», а Вы бы ответили, что лучше лететь на дирижабле, то Вас бы просто не поняли (может быть, кроме тех, кто стоит в московской пробке). Первоначально метод работал на совсем другом языке.
Вообще я не смогу Вас переубедить, когда Вы считаете, что архитектура платформы 1С никуда не годится. Могу лишь высказать свое мнение. Разрабатывать схему БД с нуля под конкретную задачу дорого. Если сравнить со строительством: 1С – это панельные многоэтажки – дешевое жилье – средство массовой автоматизации – в тесноте, да не в обиде. Единичные организации могут нанять Нормана Фостера для точной реализации своих требований. Остальным приходится использовать дешевые массовые проекты – реляционные СУБД с жесткой объектной моделью. Кроме того, знаком с печальным опытом использования Caсhe в нескольких проектах. Глазами разработчика все выглядит совсем не так радужно, как в теории. Объектная модель 1С выдерживает проверку временем – «застроены и заселены огромные территории». Кроме того, она развивается. Недавно появилась технология внешних источников данных. И если в какой-то задаче требуется более высокая реактивность (например, биллинговые системы), теперь можно бесшовно состыковать 1С с другой СУБД. У нас, например, так сделан обмен с импортной ERP.
Но все же не хотел бы уводить разговор от основной темы – работы предлагаемых приемов в детально описанных ПРАКТИЧЕСКИХ задачах.

Конструкция "В ИЕРАРХИИ" в запросах 1С:Предприятия 8.x позволяет получить подчиненные элементы иерархического объекта конфигурации по заданному отбору. Сегодня в статье рассмотрим пример ее использования, а также действия платформы на стороне СУБД и ее влияние на производительность.

Использование

Рассмотрим простой пример использования конструкции "В ИЕРАРХИИ". При выполнении следующего запроса будут получены подчиненные элементы иерархического справочника "Товары" для переданного значения переменной "Ссылка".

В тестовой базе справочник "Товары" имеет следующие тестовые данные:

Конечно, на изображении показаны не все записи справочника. Скриншот показывает лишь структуру хранения данных в иерархическом справочнике. В таблице справочника хранятся 10 групп верхнего уровня, в каждой из них содержится 5 вложенных групп с 200 элементами в каждой.

Вернемся к тестовому запросу. Передадим в параметр "&Ссылка" ссылку на группу "Группа - 1" (см. скриншот выше). Тогда результат выполнения запроса будет выглядеть следующим образом:

Как мы видим, запрос вернул ссылку на саму верхнюю группу (переданную параметром), а также вложенные группы с находящимися в них элементами. Таким образом, использование конструкции "В ИЕРАРХИИ" позволяет удобным образом получать иерархически подчиненные данные.

Синтаксис языка запросов 1C:Предприятия и классического SQL очень похожи в некоторых моментах. Но для выражения "В ИЕРАРХИИ" нет аналога в языке запросов SQL как, например, для выражения языка запросов платформы "В" есть аналогичный SQL-оператор "IN". Поэтому интересной является работа платформы с СУБД при использовании данного оператора.

За кулисами

Итак, приступим. Для примера будем использовать написанный ранее запрос к справочнику "Товары". Анализировать действия платформы будем для двух ситуация:

В качестве параметра "&Ссылка" передадим группу верхнего уровня "Группа 1" (как это мы сделали ранее).
В параметр передадим ссылку на группу "Группа 1 - 1", вложенную в группу верхнего уровня "Группа 1".

Теперь по порядку. В первом случае платформа выполнит следующие действия на SQL-сервере:

1. Сначала выполняется SQL-запрос на получение ссылки на группу справочника, переданную в качестве параметра, и всех подчиненных ей групп. Результат помещается во временную таблицу "#tt1".

2. На втором этапе дважды выполняется одинаковый запрос:

На скриншоте подробно прокомментирован текст SQL-запроса. Если кратко, то запрос позволяет выбрать подчиненные элементы для групп, ссылки на которые находятся во временной таблице. Остается вопрос: "Зачем запрос выполняется дважды?". Тут ответ простой: сначала запрос получает подчиненные элементы для групп первого уровня, которые уже содержатся во временной таблице (см. пункт 1). Затем второй запрос получает подчиненные элементы для подчиненных групп второго уровня. Поскольку на третьем уровне иерархии не присутствует ни одна группа справочника, то данный запрос более не выполняется.

В нашем случае, второй запрос вернет пустой результат, так как для записей, находящихся на 3-м уровне иерархии, нет подчиненных элементов (там нет ни одной группы).

3. Для получения конечного результата запроса платформа формирует следующий SQL-запрос:

Результат именно этого запроса в дальнейшем может обрабатываться алгоритмами на встроенном языке платформы. Таким образом, записи во временной таблице "#tt1" используются для установки условия выборки из таблицы справочника "_Reference41".

4. На последнем шаге платформа 1С:Предприятие 8.x удаляет временную таблицу "#tt1", поскольку в дальнейшем она уже не будет использоваться.

На этом процесс выполнения оператора "В ИЕРАРХИИ" завершен. Напомню, что рассмотренная последовательность действий на SQL-сервере была выполнена, когда мы в запрос на стороне платформы передавали ссылку на группу верхнего уровня "Группа - 1". Но как поведется себя платформа, если мы в качестве параметра "&Ссылка" передадим ссылку на группу второго уровня "Группа - 1 - 1"? Все произойдет аналогичным образом, кроме следующего момента: выше, во втором этапе выполнения SQL-запросов платформой, было написано, что запрос для получения подчиненных элементов выполнялся дважды - в случае с получением подчиненных элементов для группы "Группа - 1 - 1" это не так. Запрос будет выполнен только один раз.

Дело в том, что количество запросов на получение подчиненных элементов зависит от количества групп в иерархии. Другими словами, если на уровне иерархии элементов содержится хотя бы одна группа, то будет выполнен запрос из пункта 2 .

Влияние на производительность

Неправильное использование любого оператора в запросе может привести к неоптимальной работе системы. Рассматриваемый оператор "В ИЕРАРХИИ" не исключение. Его нужно применять с осторожностью, поскольку он намного усложняет алгоритм выполнения SQL-запросов к базе и тем самым увеличивает нагрузку на сервер СУБД.

Приведу пример неоптимального запроса, который может привести к названным выше печальным последствиям:

ВЫБРАТЬ Товары. Ссылка ИЗ Справочник. Товары КАК Товары ГДЕ (Товары. Ссылка В ИЕРАРХИИ (& Ссылка) ИЛИ Товары. Ссылка В ИЕРАРХИИ (& Ссылка1) ИЛИ Товары. Ссылка В ИЕРАРХИИ (& Ссылка2) )

Как можно догадаться, запрос приведет к формированию множества SQL-запросов, что повлечет за собой снижение производительности информационной системы.

Делайте выводы!

Выводы делать Вам. Скажу лишь, что оператор "В ИЕРАРХИИ" используется платформой для системы компоновки данных, когда в условиях отбора присутствуют "В ГРУППЕ", "В ГРУППЕ ИЗ СПИСКА" и прочие. Думаю не стоит объяснять, что при неправильных манипуляциях пользователи могу поставить очень сложный отбор и повысить нагрузку на сервер 1С и СУБД в несколько раз. Давайте изменять настройки только опытным пользователям.

Ну и разумеется, при написании собственных механизмов обращайте внимание на оператор "В ИЕРАРХИИ". Очень удобный с одной стороны, и опасный с другой.

Справочники 1С — специализированный объект древа метаданных, который служит для хранения статичной информации справочного характера. Например, в типовых конфигурациях можно увидеть следующие виды: , Номенклатура, Сотрудники, Основные средства и т.д. Информация в справочниках, как правило, часто не изменяется. Справочники в дальнейшем используются практически во всех объектах учета как разрез учета или справочная информация.

Ниже мы рассмотрим настройку и проектирование справочника из конфигуратора на примере справочника «Номенклатура».

Вкладка «Основные»

На вкладке «Основные» указывается имя, синоним, представление объектов, описание назначения.

Вкладка «Иерархия справочника»

Здесь устанавливается иерархичность справочника.

Иерархия в 1С 8.3 бывает двух типов — «групп и элементов » и «элементов «. Отличается тем, что в первом случае родителем (папкой) может быть только папка (группа), а во втором случае родителем может быть и элемент.

«Размещать группы сверху» — флаг отвечает за отображение групп в форме списка.

Также в настройках можно ограничить количество групп иерархии справочника соответствующей настройкой.

Вкладка «Владельцы»

Справочник может быть подчинен другому справочнику. С точки зрения конфигурирования 1С 8.3 это значит, что у подчиненного элемента становится обязательным реквизит «Владелец». Пример такой связи справочников в типовых конфигурациях «Номенклатура — Единицы Измерения», «Контрагенты-Договоры Контрагентов».

Владельцем справочника могут также быть следующие объекты метаданных: , .

Вкладка «Данные»

Получите 267 видеоуроков по 1С бесплатно:

Самая важная вкладка с точки зрения программиста. На ней указываются реквизиты справочника.

У справочника есть набор стандартных реквизитов, которые не редактируются программистом 1С 8.2, список их можно увидеть, нажав кнопку «Стандартные реквизиты»:

Остановлюсь на каждом подробнее:

ЭтоГруппа — реквизит с типом булево, показывающий, группа это или элемент. Доступен только в иерархическом справочнике. Обратите внимание, значение этого реквизита невозможно изменить в режиме 1С: Предприятие .
Код — реквизит, тип число или строка (как правило строка). Номер, присваиваемый системой автоматически. Как правило, рассчитывается как (предыдущий код + 1). Рекомендую использовать именно строковый тип, потому как сортировка числовых значений происходит не так, как нужно. Можно использовать как представление справочника в списке и в полях ввода. Как правило, используется для поиска элемента при вводе по строке. Если Вам нужно убрать поле Код, укажите в длине строки ноль.
Наименование — реквизит, обязательный к заполнению, строкового типа. Максимальная длина строки — 150 символов. Можно использовать как представление справочника в списке и в полях ввода. Как правило, используется для поиска элемента при вводе по строке. Если Вам нужно убрать поле Наименование, укажите в длине строки ноль.
Родитель — реквизит, имеющий тип СправочникСсылка.<ИмяТекущегоСправочника>. Доступен только в иерархическом справочнике. Указывает на вышестоящего родителя в иерархии. Если Элемент или Группа находятся в корне справочника, указывается значение Справочник.<ИмяТекущегоСправочника>.ПустаяСсылка.
Владелец — ссылка на элемент-владелец текущего элемента (группы) справочника. Доступен только в подчиненном справочнике 1С .
ПометкаУдаления — реквизит с типом булево. Отвечает за отображение «пометки удаления» в системе. Помеченный на удаление элемент считается непригодным к использованию, однако на нём могут оставаться старые движения в документах.
Ссылка — поле строкового типа. В этом реквизите хранится уникальный идентификатор объекта — GUID. То, что в системе мы видим в визуальном отображении под название «ссылка», — это всего лишь представление объекта. Невозможно изменить.
Предопределенный — тип булево, отображает, является ли элемент предопределенным, об этом позже. Невозможно изменить.

На вкладке «Данные» так же указывается представление справочника в системе, до версии 8.2.16 представление могло быть лишь Кодом или Наименованием. В свежих версиях платформы (начиная с 8.3) представление можно описать самостоятельно в модуле менеджера с помощью обработчика «ОбработкаПолученияПредставления».

Вкладка «Нумерация»

Здесь указываются настройки справочника по части нумерации. Рекомендуется использовать именно автонумерацию. Контроль уникальности — флаг, который помогает, если нужно, сделать код уникальным. Если с установленным флагом Вы попытаетесь записать элемент справочника с неуникальным кодом, в 1С Вы получите сообщение «Код справочника стал неуникальным».

Серия кодов — определяет, как нумеровать справочник, можно ввести нумерацию справочника в разрезе владельца. Например, у контрагента «Рога и копыта» будет иметься своя нумерация договоров — «1, 2, 3» и тд.

Вкладка «Формы»

Тут описываются формы для справочника. Если конфигурация запускается как в обычном, так и управляемом режиме, тогда вкладок с формами по умолчанию будет две: «основные» и «дополнительные» — для обычного и управляемого приложения разные.

На этой странице есть немаловажное свойство справочника — ««. Это очень удобная функция 1С 8, позволяющая при заполнении данных в поле ввода не заходить в справочник, а набрать его наименование, код или т.п. и выбрать из выпадающего списка нужный элемент. Выглядит это так:

Вкладка «Прочее»

На вкладке можно получить быстрый доступ к основным модулям справочника — модулю объекта и модулю менеджера.

На странице можно также определить список предопределенных элементов справочника. Это элементы, которые невозможно удалить в режиме Предприятия. К предопределенным элементам можно обратиться в конфигураторе напрямую, по имени, например: Справочники.Номенклатура.Услуга.

На этой вкладке также определяется режим блокировки — автоматический или управляемый. Использование полнотекстового поиска, а также справочная информация о справочнике, доступная в режиме 1С: Предприятия.

В данном разделе показаны примеры решения типовых задач при работе с иерархическими справочниками.

Получение элементов иерархического справочника, находящихся в подчинении заданной группы

Для получения подчиненных элементов иерархического справочника в языке запросов предусмотрена конструкция В ИЕРАРХИИ. Пример использования В ИЕРАРХИИ:

ВЫБРАТЬ
Номенклатура.Код,
Номенклатура.ЗакупочнаяЦена
ИЗ

В данном примере будут получены все записи справочника Номенклатура, находящиеся в группе &Группа, включая ее саму, ее подчиненные группы и элементы, принадлежащие подчиненным группам.

Если же нас интересуют только элементы и группы, находящиеся непосредственно в заданной группе, то такие элементы мы можем получить установив условие на поле Родитель. Пример:

ВЫБРАТЬ
Номенклатура.Код,
Номенклатура.Наименование КАК Наименование,
Номенклатура.ЗакупочнаяЦена
ИЗ
Справочник.Номенклатура КАК Номенклатура

ГДЕ
Номенклатура.Родитель = &Группа

Такой запрос выберет группы и элементы, находящиеся в подчинении группы со ссылкой &Группа.

Проверка наличия подчиненных элементов у элемента справочника

Для проверки наличия подчиненных записей элемента справочника можно пользоваться запросом, аналогичным представленному:

В данном примере ссылка элемента, для которого необходимо проверить наличие дочерних элементов, записывается в параметр запроса "Родитель". После выполнения такого запроса необходимо проверить результат на пустоту. Если результат не пустой, то подчиненные записи есть. Иначе - нет. Пример:

Если Запрос.Выполнить().Пустой() Тогда
Сообщить("Зписей нет");
Иначе
Сообщить("Записи есть");
КонецЕсли;

Получение всех родителей элемента

В языке запросов не предусмотрено специальных средств для получения всех родителей элемента. Для выполнения задачи можно воспользоваться иерархическими итогами, однако получение иерархических итогов оптимизировано для построения итогов большого количества записей, и не вполне эффективно для получения родителей одного элемента. Для более эффективного получения всех родительских записей элемента, рекомендуется перебирать в цикле его родителей небольшими порциями. Пример:

ТекущийЭлементНоменклатуры = ЭлементНоменклатура;

Пока Истина Цикл
Запрос.УстановитьПараметр("ТекущийЭлементНоменклатуры", ТекущийЭлементНоменклатуры);
Результат = Запрос.Выполнить();
Если Результат.Пустой() Тогда
Прервать;
КонецЕсли;
Выборка = Результат.Выбрать();
Выборка.Следующий();
Для НомерКолонки = 0 По Результат.Колонки.Количество() - 1 Цикл
ТекущийЭлементНоменклатуры = Выборка[НомерКолонки];
Прервать;
Иначе
Сообщить(ТекущийЭлементНоменклатуры);
КонецЕсли;
КонецЦикла;

Если ТекущийЭлементНоменклатуры = Справочники.Номенклатура.ПустаяСсылка() Тогда
Прервать;
КонецЕсли;
КонецЦикла;

В данном примере в окно служебных сообщений выводятся все родители для ссылки, записанной в переменную ЭлементНоменклатура. В цикле выбирается по 5 родителей ссылки.

Если число уровней в справочнике ограничено и невелико, то возможно получение всех родителей одним запросом без цикла.

Вывод иерархического справочника в отчет

Для вывода иерархического справочника в отчет с сохранением иерархии необходимо пользоваться запросом аналогичным следующему:

ВЫБРАТЬ
Номенклатура.Код,
Номенклатура.Наименование КАК Наименование,
Номенклатура.ЗакупочнаяЦена
ИЗ
Справочник.Номенклатура КАК Номенклатура
УПОРЯДОЧИТЬ ПО
Наименование ИЕРАРХИЯ

Данный запрос выбирает все записи из справочника и производит упорядочивание по иерархии. Результат будет упорядочен по наименованию, с учетом иерархии.

Для того чтобы группы справочника размещались выше элементов необходимо в данном запросе заменить предложение УПОРЯДОЧИТЬ ПО на следующее:

УПОРЯДОЧИТЬ ПО
Номенклатура.ЭтоГруппа ИЕРАРХИЯ,
Наименование

Результат по-прежнему будет упорядочен по иерархии, однако группы будут располагаться выше элементов.

Возможна также замена предложения УПОРЯДОЧИТЬ ПО на предложение АВТОУПОРЯДОЧИВАНИЕ. В этом случае результат будет упорядочен в соответствии с настройками справочника, т.е. если в справочнике указано, что группы должны располагаться выше элементов, то они будут расположены выше.

Получить иерархическую структуру справочника также возможно и при помощи итогов.

ВЫБРАТЬ
Номенклатура.Код,
Номенклатура.Наименование КАК Наименование,
Номенклатура.ЗакупочнаяЦена

ИЗ Справочник.Номенклатура КАК Номенклатура

ГДЕ
(Номенклатура.ЭтоГруппа = ЛОЖЬ)

УПОРЯДОЧИТЬ ПО Наименование

Получение итогов по иерархии

Для получения итогов по иерархии в запросе необходимо в предложении ИТОГИ ПО указать ключевое слово ИЕРАРХИЯ после указания поля, по которому будет рассчитываться итоги. Пример отчета "Обороты номенклатуры" с получением итогов по иерархии:

ВЫБРАТЬ

ИЗ

Номенклатура ИЕРАРХИЯ

В результате данного запроса будут рассчитаны итоги не только для каждой номенклатуры, но и для групп, к которым принадлежит та или иная номенклатура.

В случае, когда не нужны итоги по элементам, а нужны итоги только по группам, нам необходимо использовать в итогах конструкцию ТОЛЬКО ИЕРАРХИЯ. Пример:

ВЫБРАТЬ
УчетНоменклатурыОбороты.Номенклатура КАК Номенклатура,
УчетНоменклатурыОбороты.Номенклатура.Представление,
УчетНоменклатурыОбороты.КоличествоОборот КАК КоличествоОборот
ИЗ
РегистрНакопления.УчетНоменклатуры.Обороты КАК УчетНоменклатурыОбороты
ИТОГИ СУММА(КоличествоОборот) ПО
Номенклатура ТОЛЬКО ИЕРАРХИЯ

В результате данного запроса будут итоговые записи только для групп номенклатуры.

Что такое справочник 1с и для чего он нужен? Справочник хранит условно-постоянную информацию, т.е. информация, которая на длительном промежутке времени почти не меняется. Например, справочник "Номенклатура" содержит перечень продаваемых или выпускаемых товаров. Также справочник может содержать множество свойств описывающих элемент справочника.

Если взять для сравнения пол человека, то здесь список ограничен и не изменен, поэтому для него лучше подходит перечисление.

Создав новый справочник мы увидим следующую картину.

Рассмотрим все его закладки.

Основные

Здесь указывается имя (идентификатор в базе) и синоним (пользовательское название справочника). Необязательным является комментарий, который может пояснять назначение справочника или описывать его особенности.

Иерархия

На этой закладке можно настроить глубину вложенности элементов справочника. При помощи данной настройки удобно разграничивать и детализировать элементы по каким-то критериям. Например, товары "Шкафы" в одной группе, а товары "Столы" в другой. По умолчанию при создании справочник представляет список элементов . Если поставить флажок Иерархический справочник, то каждый элемент может быть подчинен другому элементу (группе). Ниже указаны варианты настройки этой закладки и изменение отображения в пользовательском режиме.

Вид иерархии:

Иерархия групп и элементов

При данной настройке вложенность элементов может быть только в группы (папки).

Здесь, как Вы видите, у всех элементов и групп стали одинаковые значки, и вложенность может быть у любого элемента.

Размещать группы сверху

При установке этого флажка группы всегда будут вверху, иначе будут расположены в порядке сортировки, например, так:

Ограничение количества уровней иерархии

Если здесь флажок не установлен, то вложенность неограниченна.

Если флажок установлен, то ниже можно указать количество уровней.

Владельцы

На закладке владельцы могут быть указаны другие справочники по отношению, к которым данный является подчиненным. Схема отношений подчиненных справочников похожа на схему отношений иерархического справочника, только здесь в качестве родителя выступает другой справочник и называется владельцем. В типовых конфигурациях хорошим примером является подчинение справочника "Договоры" справочнику "Контрагенты", т.к. не может быть договора не принадлежащего какому-либо контрагенту.

В поле "Список владельцев справочника" указывается список справочников, которым принадлежат элементы данного справочника.

Ниже в поле «Использование подчинения» указывается, чему будут подчиняться элементы данного справочника.

Как узнать программно справочник иерархический или нет

Для этого нужно обратиться к метаданным

ЭтоИерархическийСправочник = Метаданные.Справочники.Контрагенты.Иерархический;

Продолжение следут…