1с получить уровень справочника в запросе 1с
Вариант получения запросом структуры иерархического справочника в виде плоской таблицы с динамическим количеством колонок по фактическому максимальному уровню иерархии элементов/групп справочника.
Допустим имеем иерархический справочник "Подразделения" с неизвестным (неограниченным) уровнем вложенности:
И требуется получить таблицу следующей структуры:
Подразделение | Уровень 0 | Уровень 1 | Уровень 2 | . | Уровень Макс-1 | Уровень Макс |
Подр1 | Подр1 | |||||
Подр11 | Подр1 | Подр11 | ||||
Подр12 | Подр1 | Подр12 | ||||
Подр121 | Подр1 | Подр12 | Подр121 | |||
Подр122 | Подр1 | Подр12 | Подр122 | |||
. | ||||||
ПодрN | ПодрN |
Предлагаю следующий вариант решения:
// Определим максимальный уровень иерархии справочника
Запрос = Новый Запрос ;
Запрос . Текст =
"ВЫБРАТЬ
| Подразделения.Ссылка
|ИЗ
| Справочник.Подразделения КАК Подразделения" ;
Результат = Запрос . Выполнить ();
ВыборкаДетальныеЗаписи = Результат . Выбрать ();
МаксУровень = 0 ;
Пока ВыборкаДетальныеЗаписи . Следующий () Цикл
МаксУровень = Макс ( ВыборкаДетальныеЗаписи . Ссылка . Уровень (), МаксУровень );
КонецЦикла;
// Соберем текст запроса для получения следующей структуры полей выборки:
// Подразделение, Уровень0, Уровень1, . УровеньN-1, УровеньN.
// где N - максимальный уровень иерархии.
// Начало текста запроса
Запрос . Текст =
"ВЫБРАТЬ
| *
|ИЗ
| (" ;
// Нулевой уровень иерархии
ТекущийУровень = 0 ;
Запрос . Текст = Запрос . Текст + "
|ВЫБРАТЬ
| Подразделения.Ссылка КАК Подразделение
| , Подразделения.Ссылка КАК Уровень0" ;
Для Сч = 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
| , ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка) КАК Уровень" + Сч ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + "
|ИЗ
| Справочник.Подразделения КАК Подразделения
|ГДЕ
| Подразделения.Родитель = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)" ;
// Последующие уровни иерархии.
Для ТекущийУровень = 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| Подразделения.Ссылка КАК Подразделение" ;
Для Сч1 = 0 По ТекущийУровень - 1 Цикл
Запрос . Текст = Запрос . Текст + "
| , Подразделения" ;
Для Сч2 = 1 по ТекущийУровень - Сч1 Цикл
Запрос . Текст = Запрос . Текст + ".Родитель" ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + " КАК Уровень" + Сч1 ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + "
| , Подразделения.Ссылка КАК Уровень" + ТекущийУровень ;
Для Сч = ТекущийУровень + 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
| , ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка) КАК Уровень" + Сч ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + "
|ИЗ
| Справочник.Подразделения КАК Подразделения
|ГДЕ
| Подразделения.Родитель" ;
Для Сч2 = 1 по ТекущийУровень Цикл
Запрос . Текст = Запрос . Текст + ".Родитель" ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + " = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)" ;
КонецЦикла;
// Финиш текста запроса
Запрос . Текст = Запрос . Текст + "
| ) КАК Структура
|УПОРЯДОЧИТЬ ПО
| Структура.Уровень0.Наименование" ;
Для Сч = 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
| , Структура.Уровень" + Сч + ".Наименование" ;
КонецЦикла;
Результат = Запрос . Выполнить ();
ВыборкаДетальныеЗаписи = Результат . Выбрать ();
Пока ВыборкаДетальныеЗаписи . Следующий () Цикл
// Обход выборки.
КонецЦикла;
У данного решения есть минусы, которые я пока не смог обойти:
1. Первоначально надо получить максимальный уровень иерархии справочника, а сделать это запросом я не смог. Пришлось предварительно перебирать все элементы и проверять их уровень.
2. При большом максимальном уровне вложенности может быть будут проблемы с запросом (в местах, где множится ". Родитель.Родитель.Родитель. "). Я тестировал на 20 уровне вложенности.
Допустим имеем иерархический справочник "Подразделения" с неизвестным (неограниченным) уровнем вложенности:
И требуется получить таблицу следующей структуры:
Подразделение | Уровень 0 | Уровень 1 | Уровень 2 | . | Уровень Макс-1 | Уровень Макс |
Подр1 | Подр1 | |||||
Подр11 | Подр1 | Подр11 | ||||
Подр12 | Подр1 | Подр12 | ||||
Подр121 | Подр1 | Подр12 | Подр121 | |||
Подр122 | Подр1 | Подр12 | Подр122 | |||
. | ||||||
ПодрN | ПодрN |
Предлагаю следующий вариант решения:
// Определим максимальный уровень иерархии справочника
Запрос = Новый Запрос ;
Запрос . Текст =
"ВЫБРАТЬ
| Подразделения.Ссылка
|ИЗ
| Справочник.Подразделения КАК Подразделения" ;
Результат = Запрос . Выполнить ();
ВыборкаДетальныеЗаписи = Результат . Выбрать ();
МаксУровень = 0 ;
Пока ВыборкаДетальныеЗаписи . Следующий () Цикл
МаксУровень = Макс ( ВыборкаДетальныеЗаписи . Ссылка . Уровень (), МаксУровень );
КонецЦикла;
// Соберем текст запроса для получения следующей структуры полей выборки:
// Подразделение, Уровень0, Уровень1, . УровеньN-1, УровеньN.
// где N - максимальный уровень иерархии.
// Начало текста запроса
Запрос . Текст =
"ВЫБРАТЬ
| *
|ИЗ
| (" ;
// Нулевой уровень иерархии
ТекущийУровень = 0 ;
Запрос . Текст = Запрос . Текст + "
|ВЫБРАТЬ
| Подразделения.Ссылка КАК Подразделение
| , Подразделения.Ссылка КАК Уровень0" ;
Для Сч = 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
| , ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка) КАК Уровень" + Сч ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + "
|ИЗ
| Справочник.Подразделения КАК Подразделения
|ГДЕ
| Подразделения.Родитель = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)" ;
// Последующие уровни иерархии.
Для ТекущийУровень = 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| Подразделения.Ссылка КАК Подразделение" ;
Для Сч1 = 0 По ТекущийУровень - 1 Цикл
Запрос . Текст = Запрос . Текст + "
| , Подразделения" ;
Для Сч2 = 1 по ТекущийУровень - Сч1 Цикл
Запрос . Текст = Запрос . Текст + ".Родитель" ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + " КАК Уровень" + Сч1 ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + "
| , Подразделения.Ссылка КАК Уровень" + ТекущийУровень ;
Для Сч = ТекущийУровень + 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
| , ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка) КАК Уровень" + Сч ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + "
|ИЗ
| Справочник.Подразделения КАК Подразделения
|ГДЕ
| Подразделения.Родитель" ;
Для Сч2 = 1 по ТекущийУровень Цикл
Запрос . Текст = Запрос . Текст + ".Родитель" ;
КонецЦикла;
Запрос . Текст = Запрос . Текст + " = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)" ;
КонецЦикла;
// Финиш текста запроса
Запрос . Текст = Запрос . Текст + "
| ) КАК Структура
|УПОРЯДОЧИТЬ ПО
| Структура.Уровень0.Наименование" ;
Для Сч = 1 По МаксУровень Цикл
Запрос . Текст = Запрос . Текст + "
| , Структура.Уровень" + Сч + ".Наименование" ;
КонецЦикла;
Результат = Запрос . Выполнить ();
ВыборкаДетальныеЗаписи = Результат . Выбрать ();
Пока ВыборкаДетальныеЗаписи . Следующий () Цикл
// Обход выборки.
КонецЦикла;
У данного решения есть минусы, которые я пока не смог обойти:
1. Первоначально надо получить максимальный уровень иерархии справочника, а сделать это запросом я не смог. Пришлось предварительно перебирать все элементы и проверять их уровень.
2. При большом максимальном уровне вложенности может быть будут проблемы с запросом (в местах, где множится ". Родитель.Родитель.Родитель. "). Я тестировал на 20 уровне вложенности.
Для получения подчиненных элементов иерархического справочника в языке запросов предусмотрена конструкция В ИЕРАРХИИ :
В данном примере будут получены все записи справочника Номенклатура , находящиеся в группе &Группа , включая ее саму, ее подчиненные группы и элементы, принадлежащие подчиненным группам.
Если же нас интересуют только элементы и группы, находящиеся непосредственно в заданной группе, то такие элементы мы можем получить, установив условие на поле Родитель :
Такой запрос выберет группы и элементы, находящиеся в подчинении группы со ссылкой &Группа .
Проверка наличия подчиненных элементов у элемента справочника
Для проверки наличия подчиненных записей элемента справочника можно пользоваться запросом, аналогичным представленному:
В данном примере ссылка элемента, для которого необходимо проверить наличие дочерних элементов, записывается в параметр запроса &Родитель . После выполнения такого запроса необходимо проверить результат на пустоту. Если результат не пустой, то подчиненные записи есть. Иначе — нет.
Получение всех родителей элемента
В языке запросов не предусмотрено специальных средств для получения всех родителей элемента. Для выполнения задачи можно воспользоваться иерархическими итогами, однако получение иерархических итогов оптимизировано для построения итогов большого количества записей, и не вполне эффективно для получения родителей одного элемента. Для более эффективного получения всех родительских записей элемента, рекомендуется перебирать в цикле его родителей небольшими порциями.
Если число уровней в справочнике ограничено и невелико, то возможно получение всех родителей одним запросом без цикла.
Вывод иерархического справочника в отчет
Для вывода иерархического справочника в отчет с сохранением иерархии необходимо пользоваться запросом аналогичным следующему:
Данный запрос выбирает все записи из справочника и производит упорядочивание по иерархии. Результат будет упорядочен по наименованию, с учетом иерархии.
Для того, чтобы группы справочника размещались выше элементов необходимо в данном запросе заменить предложение УПОРЯДОЧИТЬ ПО на следующее:
Результат по-прежнему будет упорядочен по иерархии, однако группы будут располагаться выше элементов.
Возможна также замена предложения УПОРЯДОЧИТЬ ПО на предложение АВТОУПОРЯДОЧИВАНИЕ . В этом случае результат будет упорядочен в соответствии с настройками справочника, т.е. если в справочнике указано, что группы должны располагаться выше элементов, то они будут расположены выше.
Получить иерархическую структуру справочника также возможно и при помощи итогов:
Получение итогов по иерархии
Для получения итогов по иерархии в запросе необходимо в предложении ИТОГИ ПО указать ключевое слово ИЕРАРХИЯ после указания поля, по которому будет рассчитываться итоги. Пример отчета «Обороты номенклатуры» с получением итогов по иерархии:
В результате данного запроса будут рассчитаны итоги не только для каждой номенклатуры, но и для групп, к которым принадлежит та или иная номенклатура.
В случае, когда не нужны итоги по элементам, а нужны итоги только по группам, необходимо использовать в итогах конструкцию ТОЛЬКО ИЕРАРХИЯ :
В результате данного запроса будут итоговые записи только для групп номенклатуры.
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Войдите как ученик, чтобы получить доступ к материалам школы
Язык запросов 1С 8.3 для начинающих программистов: операторы МЕЖДУ и В
Автор уроков и преподаватель школы: Владимир Милькин
Логический оператор МЕЖДУ
Оператор МЕЖДУ позволяет проверить, входит ли значение выражения, указанного слева от него, в диапазон , указанный справа ( вместе с границами диапазона , то есть включительно).
Таким образом, вместо
можно написать более лаконичное
А результат будет один и тот же:
Если же необходимо наоборот выбрать всю еду, калорийность которой не входит в диапазон [200;300], то подойдёт следующая форма отрицания (появилась частица НЕ):
Оператор МЕЖДУ можно применять не только к числовым диапазонам. С датами он также хорошо работает:
Логический оператор В
Проверка совпадения с одним из перечисленных
Оператор В позволяет проверить, совпадает ли значение выражения, указанного слева от него, с одним из значений , описанных справа .
Таким образом, вместо
можно написать более лаконичное
А результат будет один и тот же:
Если же необходимо наоборот выбрать всю еду, цвет которой не совпадает ни с одним значением из списка, то подойдёт следующая форма отрицания (появилась частица НЕ):
Проверка совпадения значения с одним из результата запроса
Пусть нам требуется выбрать из базы только те цвета, которые присутствуют в описании еды. Таким образом, в отобранном списке не должен присутствовать, например, чёрный цвет, так как еды чёрного цвета в нашей базе нет. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.
Один из вариантов как это можно сделать - использовать логический оператор В, справа от которого будет находиться подзапрос, выбирающий названия цветов из всех записей справочника Еда:
В качестве отступления упомяну, что из внутреннего запроса есть возможность обращаться к полям внешнего запроса.
Опять же, для этой формы оператора В, также доступно использование частицы НЕ перед ним.
Проверка принадлежности по иерархии для справочников
Для справочников проверка может осуществляться и на принадлежность по иерархии.
Для начала давайте рассмотрим пример иерархического справочника. Откройте справочник "Города" в нашей базе:
Обратите внимание, что его элементы отличаются от других справочников (Еда, Цвета, Вкусы) наличием жёлтых папок. Это группы справочника .
Группы отличаются от обычных элементов тем, что могут включать в себя другие группы и элементы. Подобно тому как папки включают в себя другие папки и файлы.
Чтобы просмотреть содержимое группы, сделайте на ней двойной щелчок мышкой:
Чтобы выйти на уровень выше, снова сделайте двойной щелчок по группе:
Таким образом иерархический справочник может содержать как обычные элементы (например, Рио-де-Жанейро, Салвадор), так и группы (например, Бразилия, Индия). Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.
У каждого элемента (будь то группа или обычный элемент) может быть родитель. Например, родителем элемента Рио-де-Жанейро является группа Бразилия:
И это верно, потому что Рио-де-Жанейро входит в состав группы Бразилия в иерархии справочника:
Теперь давайте напишем такой запрос, который будет запрашивать выбранную группу-страну и все элементы-города, входящие в неё.
Обратите внимание на то, что в тексте запроса перед именем ГруппаСтрана стоит амперсанд (&). Имена с амперсандом автоматически распознаются системой как параметры , значение которых должно быть задано перед выполнением запроса.
После того, как мы вставим этот запрос в консоль и нажмем кнопку "Выполнить" для обновления, у нас появится возможность задать этот параметр:
Выберите в качестве его значения группу "Россия" (кнопка Выбрать):
Если теперь мы нажмём кнопку "Выполнить", то результат запроса будет следующим:
В результат запроса попала сама группа (Россия) и все элементы, которые входят в её состав (Пермь, Красноярск и Воронеж).
Если вместо России, выбрать "Бразилия", то результат будет таким:
Таким образом результатом оператора В ИЕРАРХИИ будет ИСТИНА, если значение выражения слева является ссылкой на элемент справочника и входит во множество значений справа (Бразилия) или иерархически принадлежит какой-нибудь группе, содержащейся в этом множестве (Сан-Паулу, Рио-де-Жанейро, Салвадор).
В качестве множества значений, на совпадение с которыми выполняется проверка, может фигурировать и результат запроса. В этом случае справа от оператора В необходимо указать описание запроса:
Для оператора В ИЕРАРХИИ также доступно использование частицы НЕ перед ним.
Структура кода запроса в 1С легко изучить. Начнем от простого запроса к более сложным вариантам.
Выбрать
Любой запрос имеет команду ВЫБРАТЬ, после которой могут идти дополнительные параметры выборки, которые позднее рассмотрим отдельно:
ПЕРВЫЕ
РАЗЛИЧНЫЕ
РАЗРЕШЕННЫЕ
Далее идет список полей выборки.
Простейший запрос в 1С может иметь такой вид:
Данный запрос вернет таблицу, состоящую из одной строки и одной колонки, в которой будет числовое значение равное 1.
Усложняем запрос: добавим еще поля, разделив их запятой
ВЫБРАТЬ 1, 2, "3"
Этот запрос также вернет 1 строку, но уже с 3 колонками, две из которых содержат числа, а 3 строковое.
Система 1С автоматически присвоит этим полям имена «Поле1», «Поле2», «Поле3», что неплохо, но не всегда удобно, ведь полей может быть значительно больше.
Поэтому в языке запросов существует специальный оператор именования КАК , который идет после поля,
Предыдущий запрос можно представить в таком виде:
ВЫБРАТЬ 1 КАК Один, 2 КАК Два, "3" КАК ЦифраТриСтрокой
Имена полей задаются по правилам именования переменных: не могут начинаться со строки, содержать пробелы, не должны повторятся в одной выборке, а также отсутствовать (они поставятся автоматически).
Возможно опускать слово КАК, но лучше такой вариант не использовать, так как конструктор запросов это исправит:
ВЫБРАТЬ ссылка ссылка ИЗ Справочник.ФизическиеЛица
Такие простые запросы конечно имеют место в реальных условиях, как вспомогательные таблицы, но обычно требуется выборка из какого-то источника.
Для этого существует оператор ИЗ , в которой источник данных может быть реальной таблицей из базы данных, а также виртуальными таблицами, которые существуют к некоторым таким таблицами или же к временным таблицам.
Пример простого запроса к реальной таблице справочника:
ВЫБРАТЬ ссылка ИЗ Справочник.Контрагенты
В таких запросах мы можем указать конкретные поля, которые нам нужны или указать *.
ВЫБРАТЬ * ИЗ Справочник.Контрагенты
Такой вариант универсальный, но избыточное количество полей замедляет и усложняет их разбор позднее.
Вложенные таблицы
Также возможно обращение к вложенной таблице:
ВЫБРАТЬ * ИЗ Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
Как видите, здесь мы впервые использовали именование таблицы, которое также упрощает работу при написании запроса, это дает возможность обращения по этому имени к полям:
ВЫБРАТЬ КИ.Ссылка ИЗ Справочник.Контрагенты.КонтактнаяИнформация КАК КИ //так иногда будет короче
Особенностью запросов к вложенным таблицам является то, что возможно обращение к родительской таблице через поле ссылка через точку.
ВЫБРАТЬ Ссылка.Наименование ИЗ Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
Подобная возможность используется и при обращении к реквизитам ссылочных полей: система сама неявно соединит нужные таблицы, но использование такого обращения следует использовать реже (особенно при указании условий в виртуальных таблицах или условий соединения таблиц).
ВЫБРАТЬ Ссылка, ОсновнойДоговор.Наименование КАК НазваниеДоговора ИЗ Справочник.Контрагенты
В данном случае поле ОсновнойДоговор может быть пустым, тогда обращение к его наименованию не вызовет ошибку, но значение будет NULL, поэтому рекомендуется использовать ЕстьNULL.
Дополнительно
Вернемся к запросу
ВЫБРАТЬ КИ.Ссылка ИЗ Справочник.Контрагенты.КонтактнаяИнформация КАК КИ
В данном запросе, мы не выбираем поля из самой подтаблицы КонтактнаяИнформация, и в случае если в ней несколько строк, то в выборку попадут несколько строк с одинаковым содержимым.
Чтобы избежать этого можно воспользоваться служебным словом РАЗЛИЧНЫЕ: система устранит все дубли автоматически.
ВЫБРАТЬ РАЗЛИЧНЫЕ КИ.Ссылка ИЗ Справочник.Контрагенты.КонтактнаяИнформация КАК КИ
Если мы хотим ограничить результат выборки количеством записей (например сотней) используем ПЕРВЫЕ, но использовать ее необходимо с осторожностью (иногда эта команда срабатывает раньше, чем применяются условия запросов), следует тестировать запросы основательно.
ВЫБРАТЬ ПЕРВЫЕ 100 КИ.Ссылка ИЗ Справочник.Контрагенты.КонтактнаяИнформация КАК КИ
Последнее, что следует упомянуть при изучении основ выборки запросов 1С:
Команду «ПОМЕСТИТЬ », позволяющую помещать выборку в таблицу для многократного последующего использования.
Источник может также выборкой из запроса, указанной в скобках:
ВЫБРАТЬ * ИЗ (ВЫБРАТЬ * ИЗ Справочник.Контрагенты) КАК ВложеннаяТаблица
Использование вложенных запросов имеет смысл в случае, если в нем данные как-либо предварительно обрабатываются, а не как в вышеуказанном примере, где это излишне.
В целом, использование временных таблиц и вложенных запросов замедляет выборку, но парадокс в том, что их использование может дать нужный сигнал оптимизатору запросов и эффект получается положительный.
При больших исходных данных, следует проверять оба варианта использования, на предмет скорости исполнения, для принятия окончательного варианта запроса.
Регистр команд, имен и полей запроса не имеет значение: Выбрать и ВыБРатЬ равносильны.
Часто простая смена заголовка изменяет эффективность рекламы в 5-10 раз.
— Клод Хопкинс
Читайте также: