Как составить прямой запрос 1С++ с отбором по группе контрагентов и признаку проведения?

← На главную

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

Особенности выборки по иерархии и шапке документов в 1С++

В реляционной базе данных SQL документы и справочники 1С 7.7 хранятся в отдельных таблицах:

Таблица шапки документа (метапарсер 1С++ обозначает ее как $Документ.ИмяДокумента) содержит реквизиты конкретного вида документа, включая ссылку на контрагента.
Общий журнал документов 1SJOURN хранит общие реквизиты: дату и время (date_time_doc), признак проведения (битовая маска в поле closed), пометку на удаление (ismark) и вид документа (iddocdef).
Таблица справочника ($Справочник.Контрагенты) хранит иерархию элементов через поле parentid. В отличие от штатного языка 1С («В Иерархии»), чистый SQL не разворачивает папки автоматически.

Способ 1. Использование временной таблицы и метода УложитьСписокОбъектов

Рассмотрим классический и наиболее распространенный подход: предварительное заполнение списка значений всеми элементами выбранной группы и передача их во временную SQL-таблицу.

Разберем по шагам реализацию этого механизма:

Создаем объект СписокЗначений и заполняем его всеми подчиненными элементами выбранной группы контрагентов.
Используем специальный метод объекта ODBCRecordSet — УложитьСписокОбъектов(), который создает временную таблицу на SQL-сервере (по умолчанию с полем Val).
Выполняем запрос с внутренним соединением (INNER JOIN) к временной таблице.
Соединяем таблицу шапки с журналом 1SJOURN по идентификатору iddoc и проверяем статус проведения с помощью побитового сравнения жур.closed & 1 = 1.
Для максимальной производительности и задействования кластерных индексов обязательно передаем условие по виду документа жур.iddocdef = $ВидДокумента.МойДок.

Посмотрим на пример кода 1С 7.7:


ЗапросSQL = СоздатьОбъект("ODBCRecordSet");

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

// Передаем список во временную таблицу SQL #Contr
ЗапросSQL.УложитьСписокОбъектов(СпКонтрагентов, "#Contr", "Контрагенты");

// Формируем текст запроса
ТекстЗапроса = "
|SELECT 
|    жур.iddoc as [Документ $Документ.МойДок],
|    $док.Контрагент as [Контрагент $Справочник.Контрагенты]
|FROM 
|    1SJOURN as жур (NOLOCK)
|INNER JOIN 
|    $Документ.МойДок as док (NOLOCK) ON док.iddoc = жур.iddoc
|INNER JOIN 
|    #Contr as фильтр ON $док.Контрагент = фильтр.Val
|WHERE 
|    жур.iddocdef = $ВидДокумента.МойДок
|    AND (жур.closed & 1 = 1)
|    AND (жур.ismark = 0)
|    AND жур.date_time_doc BETWEEN :НачДата AND :КонДата~
|";

ЗапросSQL.УстановитьТекстовыйПараметр("НачДата", ВыбНачДата);
ЗапросSQL.УстановитьТекстовыйПараметр("КонДата", ВыбКонДата);

ТЗ = ЗапросSQL.ВыполнитьИнструкцию(ТекстЗапроса);

Способ 2. Отбор через подзапрос WHERE IN

Проанализируем альтернативный синтаксис отбора по временной таблице через предикат IN. По функционалу он идентичен соединению INNER JOIN, однако на больших массивах данных соединение через JOIN чаще формирует более оптимальный план выполнения в SQL Server (например, операторы Hash Match или Merge Join):


SELECT 
    док.iddoc as [Документ $Документ.МойДок]
FROM 
    $Документ.МойДок as док (NOLOCK)
INNER JOIN 
    1SJOURN as жур (NOLOCK) ON жур.iddoc = док.iddoc
WHERE 
    жур.iddocdef = $ВидДокумента.МойДок
    AND (жур.closed & 1 = 1)
    AND (жур.ismark = 0)
    AND $док.Контрагент IN (SELECT Val FROM #Contr)

Способ 3. Рекурсивный разворот группы прямо в SQL (CTE)

Если в базе данных содержится огромное дерево контрагентов, обход элементов встроенным языком 1С может работать медленно. В таком случае разворот группы можно выполнить полностью на стороне SQL Server с помощью рекурсивного выражения (CTE), без использования СписокЗначений и метода УложитьСписокОбъектов:


ТекстЗапроса = "
|WITH TreeContr (id, isfolder) AS (
|    -- Якорная часть: выбираем саму группу
|    SELECT id, isfolder 
|    FROM $Справочник.Контрагенты (NOLOCK)
|    WHERE id = :ВыбГруппа
|    
|    UNION ALL
|    
|    -- Рекурсивная часть: выбираем всех потомков
|    SELECT c.id, c.isfolder
|    FROM $Справочник.Контрагенты c (NOLOCK)
|    INNER JOIN TreeContr p ON c.parentid = p.id
|)
|SELECT 
|    жур.iddoc as [Документ $Документ.МойДок],
|    $док.Контрагент as [Контрагент $Справочник.Контрагенты]
|FROM 
|    1SJOURN as жур (NOLOCK)
|INNER JOIN 
|    $Документ.МойДок as док (NOLOCK) ON док.iddoc = жур.iddoc
|INNER JOIN 
|    TreeContr as tc ON tc.id = $док.Контрагент
|WHERE 
|    жур.iddocdef = $ВидДокумента.МойДок
|    AND tc.isfolder = 1 -- Отбираем только элементы (не папки)
|    AND (жур.closed & 1 = 1)
|    AND (жур.ismark = 0)
|";

ЗапросSQL.УстановитьТекстовыйПараметр("ВыбГруппа", ВыбГруппа.ТекущийЭлемент());
ТЗ = ЗапросSQL.ВыполнитьИнструкцию(ТекстЗапроса);

Важные рекомендации по оптимизации прямых запросов 1С++

Хинты блокировок: Всегда указывайте (NOLOCK) при чтении рабочих таблиц (1SJOURN, таблицы документов и справочников), чтобы исключить взаимные блокировки с работающими пользователями.
Типизация полей: Используйте мета-имена колонок вида [Документ $Документ.Имя] или [Контрагент $Справочник.Контрагенты] в секции SELECT, чтобы 1С++ автоматически вернула типизированные объекты 1С, а не строковые тридцатишестиричные идентификаторы.
Проверка признака проведения и удаления: Проверки одного лишь бита closed & 1 = 1 бывает недостаточно при аварийных отменах транзакций, поэтому всегда добавляйте условие на отсутствие пометки удаления: ismark = 0.
Макрос конца суток: При передаче конечной даты периода используйте синтаксис :КонДата~ — тильда заставляет парсер 1С++ развернуть дату в 23:59:59 позиции документов.