Знаходження у групі з ієрархії 1с 8. Оператор "в ієрархії" у запиті. Отримання всіх батьків елемента

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с і для чого він потрібний? Довідник зберігає умовно-постійну інформацію, тобто. інформація, яка на тривалому проміжку часу майже не змінюється. Наприклад, довідник "Номенклатура" містить перелік товарів, що продаються або випускаються. Також довідник може містити безліч властивостей, що описують елемент довідника.

Якщо взяти для порівняння стать людини, то тут список обмежений і не змінений, тому для нього краще підходить перерахування.

Створивши новий довідник, ми побачимо наступну картину.

Розглянемо усі його закладки.

Основні

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

Ієрархія

На цій закладці можна налаштувати глибину вкладення елементів довідника. За допомогою цієї настройки зручно розмежовувати та деталізувати елементи за якимись критеріями. Наприклад, товари "Шафи" в одній групі, а товари "Столи" в іншій. За умовчанням під час створення довідник представляє список елементів. Якщо поставити прапорець Ієрархічний довідник, кожен елемент може бути підпорядкований іншому елементу (групі). Нижче наведено варіанти налаштування цієї закладки та зміна відображення в режимі користувача.

Вид ієрархії:

Ієрархія груп та елементів

При цьому параметрі вкладення елементів може бути тільки в групи (папки).

Тут, як Ви бачите, у всіх елементів та груп стали однакові значки, і вкладеність може бути у будь-якого елемента.

Розміщувати групи зверху

При установці цього прапорця групи завжди будуть вгорі, інакше будуть розташовані в порядку сортування, наприклад:

Обмеження кількості рівнів ієрархії

Якщо тут прапорець не встановлений, то вкладеність необмежена.

Якщо прапорець встановлено, нижче можна вказати кількість рівнів.

Власники

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

У полі "Список власників довідника" вказується список довідників, до яких належать елементи даного довідника.

Нижче в полі «Використання підпорядкування» вказується, чому підпорядковуватимуться елементи даного довідника.

Як дізнатися програмно довідник ієрархічний чи ні

Для цього потрібно звернутися до метаданих

ЦеІєрархічнийДовідник = Метадані.Довідники.Контрагенти.Ієрархічний;

Продовження слідують…

Довідники 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С: Підприємства.

Конструкція "В ІЄРАРХІЇ" у запитах 1С:Підприємства 8.x дозволяє отримати підпорядковані елементи ієрархічного об'єкта конфігурації по заданому відбору. Сьогодні у статті розглянемо приклад її використання, а також дії платформи на боці СУБД та її вплив на продуктивність.

Використання

Розглянемо простий приклад використання конструкції "В ІЄРАРХІЇ". Під час виконання наступного запиту будуть отримані підпорядковані елементи ієрархічного довідника "Товари" для переданого значення змінної "Посилання".

Текст Запиту = " ВИБРАТИ | Товари . Посилання,| Товари . Артикул |З| Довідник . Товари ЯК|ДЕ | Товари . Посилання В ІЄРАРХІЇ(& Посилання)"

У тестовій базі довідник "Товари" має такі тестові дані:

Звичайно, на зображенні показані не всі записи довідника. Скріншот показує лише структуру зберігання даних у ієрархічному довіднику. У таблиці довідника зберігаються 10 груп верхнього рівня, у кожній з них міститься 5 вкладених груп із 200 елементами у кожній.

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

Як бачимо, запит повернув посилання на саму верхню групу (передану параметром), і навіть вкладені групи з які у них елементами. Таким чином, використання конструкції "В Ієрархії" дозволяє зручним чином отримувати ієрархічно підпорядковані дані.

Синтаксис мови запитів 1C:Підприємства та класичного SQLдуже схожі у деяких моментах. Але для виразу "В ІЄРАРХІЇ" немає аналога в мові запитів SQL, як, наприклад, для вираження мови запитів платформи "В" є аналогічний SQL-оператор "IN". Тому цікавою є робота платформи із СУБД при використанні даного оператора.

За лаштунками

Отже, почнемо. Для прикладу використовуватимемо раніше написаний запит до довідника "Товари". Аналізувати дії платформи будемо для двох ситуація:

1. Як параметр "&Посилання" передамо групу верхнього рівня "Група 1" (як це ми зробили раніше).
2. У параметр передамо посилання на групу "Група 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С і СУБД в кілька разів. Давайте змінювати установки лише досвідченим користувачам.

Та й зрозуміло, при написанні власних механізмів звертайте увагу на оператор "В ІЄРАРХІЇ". Дуже зручний з одного боку і небезпечний з іншого.

У цьому розділі наведено приклади вирішення типових завдань при роботі з ієрархічними довідниками.

Отримання елементів ієрархічного довідника, що у підпорядкуванні заданої групи

Для отримання підлеглих елементів ієрархічного довідника у мові запитів передбачено конструкцію В ІЄРАРХІЇ. Приклад використання У ІЄРАРХІЇ:

ВИБРАТИ
Номенклатура.
Номенклатура.ЗакупівельнаЦіна
З

У цьому прикладі будуть отримані всі записи довідника Номенклатура, що знаходяться в групі &Група, включаючи її саму, її підлеглі групи та елементи, що належать підлеглим групам.

Якщо ж нас цікавлять лише елементи та групи, що знаходяться безпосередньо у заданій групі, то такі елементи ми можемо отримати, встановивши умову на полі Батько. Приклад:

ВИБРАТИ
Номенклатура.
Номенклатура.Найменування ЯК Найменування,
Номенклатура.ЗакупівельнаЦіна
З
Довідник Номенклатура ЯК Номенклатура

ДЕ
Номенклатура.Батько = Група

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

Перевірка наявності підлеглих елементів у елемента довідника

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

У цьому прикладі посилання елемента, для якого необхідно перевірити наявність дочірніх елементів, записується у параметр запиту "Батько". Після цього запиту необхідно перевірити результат на порожнечу. Якщо результат не порожній, підпорядковані записи є. Інакше – ні. Приклад:

Якщо Запит.Виконати().Порожній() Тоді
Повідомити("Зписів немає");
Інакше
Повідомити("Записи є");
КінецьЯкщо;

Отримання всіх батьків елемента

У мові запитів не передбачено спеціальних засобів отримання всіх батьків елемента. Для виконання завдання можна скористатися ієрархічними результатами, проте отримання ієрархічних результатів оптимізовано для побудови результатів великої кількості записів, і не дуже ефективно для отримання батьків одного елемента. Для більш ефективного отримання всіх батьківських записів елемента, рекомендується перебирати у циклі батьків невеликими порціями. Приклад:

ПоточнийЕлементноменклатури = Елементноменклатура;

Запит = Новий Запит("ВИБРАТИ
| Номенклатура.Батько,
| Номенклатура.Батько.Батько,
| Номенклатура.Батько.Батько.
| Номенклатура. Батько. Батько. Батько. Батько,
| Номенклатура.Батьки.Батьки.Батьки.Батьки.Батьки.
|З
| Довідник Номенклатура ЯК Номенклатура
|ДЕ
| Номенклатура.Посилання = &ПоточнийЕлементНоменклатури";

Поки що Істина Цикл
Запит.ВстановитиПараметр("Поточний Елемент Номенклатури", Поточний Елемент Номенклатури);
Результат = Запит.Виконати();
Якщо Результат.Порожній() Тоді
Перервати;
КінецьЯкщо;
Вибірка = Результат.Вибрати();
Вибірка.Наступний();
Для НомерКолонки = 0 За Результат.Колонки.Кількість() - 1 Цикл
ПоточнийЕлементНоменклатури = Вибірка[НомерКолонки];
Перервати;
Інакше
Повідомити(ПоточнийЕлементНоменклатури);
КінецьЯкщо;
КінецьЦикл;

Якщо ПоточнийЕлементНоменклатури = Довідники.Номенклатура.ПустаПосилання() Тоді
Перервати;
КінецьЯкщо;
КінецьЦикл;

У цьому прикладі у вікно службових повідомлень виводяться всі батьки для посилання, записаного в змінну Елемент Номенклатура. У циклі вибирається по 5 батьків заслання.

Якщо кількість рівнів у довіднику обмежена і невелика, можливо отримання всіх батьків одним запитом без циклу.

Виведення ієрархічного довідника у звіт

Для виведення ієрархічного довідника у звіт із збереженням ієрархії необхідно користуватися запитом аналогічним до наступного:

ВИБРАТИ
Номенклатура.
Номенклатура.Найменування ЯК Найменування,
Номенклатура.ЗакупівельнаЦіна
З
Довідник Номенклатура ЯК Номенклатура
ВПОРЯДКУВАТИ ЗА
Найменування Ієрархія

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

Для того, щоб групи довідника розміщувалися вище елементів, необхідно в даному запиті замінити пропозицію ПОРЯДОЧИТИ ПО на наступне:

ВПОРЯДКУВАТИ ЗА
Номенклатура.ЦеГрупа Ієрархія,
Найменування

Результат, як і раніше, буде впорядкований за ієрархією, проте групи будуть розташовуватися вище за елементи.

Можлива також заміна пропозиції ПОРЯДОЧИТИ ПО на пропозицію автоупорядкування. І тут результат буде упорядкований відповідно до налаштуваннями довідника, тобто. якщо в довіднику зазначено, що групи повинні розташовуватися вище за елементи, то вони будуть розташовані вище.

Здобути ієрархічну структуру довідника також можливо і за допомогою підсумків.

ВИБРАТИ
Номенклатура.
Номенклатура.Найменування ЯК Найменування,
Номенклатура.ЗакупівельнаЦіна

З Довідник.Номенклатура ЯК Номенклатура

ДЕ
(Номенклатура.ЦеГрупа = БРЕХНЯ)

ВПОРЯДОЧИТИ ПО Найменування

Отримання підсумків з ієрархії

Для отримання підсумків з ієрархії у запиті необхідно в пропозиції ПІДСУМКИ ПО вказати ключове слово ІЄРАРХІЯ після вказівки поля, за яким розраховуватимуться підсумки. Приклад звіту "Обороти номенклатури" з одержанням підсумків з ієрархії:

ВИБРАТИ

З

Номенклатура ІЄРАРХІЯ

В результаті цього запиту будуть розраховані підсумки не тільки для кожної номенклатури, але й для груп, до яких належить та чи інша номенклатура.

У разі, коли не потрібні підсумки за елементами, а потрібні підсумки лише за групами, нам необхідно використовувати у підсумках конструкцію ТІЛЬКИ ІЄРАРХІЯ. Приклад:

ВИБРАТИ
ОблікНоменклатуриОбороти.Номенклатура ЯК Номенклатура,
Облік Номенклатури Обороти. Номенклатура.
ОблікНоменклатуриОбороти.КількістьОборот ЯК КількістьОборот
З
Реєстр Накопичення. Облік Номенклатури.
ПІДСУМКИ СУМА(КількістьОбіг)
Номенклатура ТІЛЬКИ ІЄРАРХІЯ

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