Как получить полный код элемента справочника в 1С 7.7 через прямой SQL-запрос?

При работе с базами данных «1С:Предприятие 7.7» через прямые запросы (например, с использованием технологии ADODB или внешней компоненты 1С++) разработчики часто сталкиваются со следующей проблемой: стандартные и удобные методы платформы, такие как встроенная функция ПолныйКод(), недоступны напрямую из языка SQL. Попытка получить полную иерархическую цепочку кодов (или наименований) для элементов многоуровневого справочника стандартным линейным запросом не дает нужного результата.

В этой статье мы подробно разберем, какими способами можно решить эту задачу, начиная от классических подходов времен MS SQL Server 2000 и заканчивая современными методами оптимизации запросов, рекурсивными вычислениями и обработкой данных на клиенте.

Способ 1. Классическое каскадное соединение таблиц (LEFT JOIN)

Рассмотрим самый очевидный и исторически первый способ решения задачи. Он заключается в последовательном левом соединении таблицы справочника самой с собой по полю связи с родителем (PARENTID). Количество таких соединений напрямую зависит от максимальной вложенности (количества уровней) справочника, заданной в конфигураторе, минус один уровень.

Представим, что у нас есть справочник с максимальной глубиной в 4 уровня. Чтобы получить полный код элемента, нам необходимо связать основную таблицу справочника с тремя «родительскими» таблицами-представлениями. Проанализируем пример такого SQL-запроса:

SELECT
    ISNULL(par3.code + '/', '') + 
    ISNULL(par2.code + '/', '') + 
    ISNULL(par1.code + '/', '') + 
    spr.code AS FullCode
FROM 
    scXX spr (NOLOCK)
LEFT JOIN 
    scXX par1 (NOLOCK) ON par1.id = spr.parentid
LEFT JOIN 
    scXX par2 (NOLOCK) ON par2.id = par1.parentid
LEFT JOIN 
    scXX par3 (NOLOCK) ON par3.id = par2.parentid

Выясним, как работает этот запрос по шагам:

1. Таблица scXX (где XX — это внутренний идентификатор таблицы справочника в СУБД) выступает в роли основного источника данных (spr).
2. Первое соединение (par1) ищет непосредственного родителя текущего элемента по условию par1.id = spr.parentid.
3. Второе соединение (par2) поднимается еще на один уровень вверх, связывая родителя первого уровня с родителем второго уровня: par2.id = par1.parentid.
4. Третье соединение (par3) находит корневой элемент ветки (родителя третьего уровня).
5. В секции SELECT мы склеиваем коды всех найденных уровней через разделитель «слэш» (/). Использование функции ISNULL критически важно: если у элемента нет родителя соответствующего уровня (например, элемент находится на 2-м уровне вложенности), SQL-сервер вернет для этого поля значение NULL. Без функции ISNULL сложение строки с NULL превратило бы весь итоговый полный код в пустое значение.

Важное улучшение: Чтобы не хардкодить структуру запроса под каждый конкретный справочник, мы можем динамически собирать текст запроса в коде 1С. Для этого достаточно прочитать свойство метаданных справочника КоличествоУровней и в цикле сгенерировать необходимое количество строк LEFT JOIN и полей склейки в секции SELECT.

Способ 2. Рекурсивные запросы (CTE) в MS SQL Server

Если ваша база данных «1С 7.7» работает на современной версии MS SQL Server (начиная с версии 2005 и новее), то ручное каскадное соединение таблиц уходит в прошлое. Мы можем применить механизм обобщенных табличных выражений (Common Table Expressions, CTE) для написания элегантного рекурсивного запроса.

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

WITH HierarchyCTE AS (
    -- Анкорная часть: выбираем корневые элементы (у которых нет родителя)
    SELECT 
        id, 
        parentid, 
        code,
        CAST(code AS VARCHAR(MAX)) AS FullCodePath
    FROM 
        scXX
    WHERE 
        parentid = '     0   ' -- Пустая ссылка в терминологии 1С 7.7

    UNION ALL

    -- Рекурсивная часть: присоединяем подчиненные элементы
    SELECT 
        child.id, 
        child.parentid, 
        child.code,
        CAST(parent.FullCodePath + '/' + child.code AS VARCHAR(MAX))
    FROM 
        scXX child
    INNER JOIN 
        HierarchyCTE parent ON child.parentid = parent.id
)
SELECT 
    id, 
    FullCodePath 
FROM 
    HierarchyCTE;

Рассмотрим подробнее принцип работы рекурсивного CTE:

• Анкорный (базовый) запрос: Выбирает элементы верхнего уровня, у которых поле parentid заполнено пробелами (в 1С 7.7 пустая ссылка типа «Справочник» представлена строкой из 9 пробелов). Для них начальный полный код равен их собственному коду.
• Рекурсивный запрос: Соединяет физическую таблицу справочника child с виртуальной таблицей parent, которая уже содержит накопленные на предыдущем шаге пути. На каждом шаге рекурсии к родительскому пути прибавляется символ / и код текущего подчиненного элемента.

Способ 3. Использование скалярной функции (UDF) на стороне SQL-сервера

Если вам приходится очень часто получать полные коды элементов в самых разных отчетах, вы можете один раз создать пользовательскую скалярную функцию (User-Defined Function, UDF) в вашей SQL-базе данных.

Создадим функцию, которая будет принимать уникальный идентификатор элемента ID и возвращать собранную строку полного кода:

CREATE FUNCTION dbo.GetFullCode77 (@ElementID CHAR(9))
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @FullCode VARCHAR(250)
    DECLARE @ParentID CHAR(9)
    DECLARE @CurrentCode VARCHAR(10)

    -- Получаем код и родителя исходного элемента
    SELECT @CurrentCode = RTRIM(code), @ParentID = parentid 
    FROM scXX 
    WHERE id = @ElementID

    SET @FullCode = @CurrentCode

    -- Поднимаемся вверх по дереву в цикле WHILE
    WHILE @ParentID <> '     0   '
    BEGIN
        SELECT @CurrentCode = RTRIM(code), @ParentID = parentid 
        FROM scXX 
        WHERE id = @ParentID

        SET @FullCode = @CurrentCode + '/' + @FullCode
    END

    RETURN @FullCode
END

После создания такой функции ваш итоговый запрос в 1С станет невероятно простым и легко читаемым:

SELECT 
    id, 
    descr, 
    dbo.GetFullCode77(id) AS FullCode 
FROM 
    scXX (NOLOCK)

Важное предупреждение: Несмотря на простоту синтаксиса, помните о производительности. Скалярные функции выполняются СУБД построчно (так называемый RBAR — Row-By-Agonizing-Row). На таблицах в десятки и сотни тысяч записей такой запрос может выполняться крайне медленно, так как для каждой строки сервер будет запускать отдельный цикл поиска родителей.

Способ 4. Гибридный метод с вычислениями в памяти клиента (1С++)

Для высоконагруженных систем и огромных справочников (например, «Номенклатура» на миллион позиций) лучшим решением является перенос тяжелых строковых операций с SQL-сервера на сторону клиента. В этом нам поможет внешняя компонента 1С++ и класс ИндексированнаяТаблицаЗначений (ИТЗ).

Посмотрим на алгоритм реализации этой схемы:

1. Мы делаем максимально простой и быстрый прямой запрос к SQL-серверу без каких-либо соединений (JOIN). Нам нужно получить только три поля: ID, ParentID и Code. Этот запрос отработает практически мгновенно.
2. Результат запроса выгружается в объект ИндексированнаяТаблицаЗначений.
3. Мы строим индексы по колонкам ID и ParentID прямо в оперативной памяти клиента. Поиск по индексированной таблице происходит за доли миллисекунды.
4. В коде 1С мы пишем быструю функцию обхода дерева, которая собирает полные коды в памяти компьютера пользователя, избавляя SQL-сервер от избыточной дисковой и вычислительной нагрузки.

Способ 5. Денормализация данных с помощью триггеров

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

Мы можем пойти по следующему пути:

• Через SQL Server Management Studio добавим в таблицу справочника scXX новое пользовательское поле, например uf_FullCode.
• Создадим триггер на операции INSERT и UPDATE для этой таблицы. При записи нового элемента или изменении его родителя триггер автоматически вычислит его полный код и запишет в поле uf_FullCode. Более того, при смене родителя у папки триггер должен рекурсивно обновить пути для всех вложенных элементов.

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

Резюме

Мы проанализировали пять различных подходов к получению полного кода элемента в иерархических справочниках 1С 7.7 через прямые запросы. Для простых задач и справочников небольшой вложенности отлично подойдет динамический сбор запроса с LEFT JOIN или рекурсивный запрос CTE. Для масштабных баз данных с целью минимизации нагрузки на СУБД рекомендуется использовать гибридную схему с Индексированной таблицей значений 1С++ или денормализацию данных.