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

При разработке отчетов в конфигурациях на базе 1С:Библиотеки стандартных подсистем (БСП), таких как ERP, УТ 11, Комплексная автоматизация или УНФ, программисты часто сталкиваются со специфической задачей. Необходимо вывести в одну таблицу (или кросс-таблицу) абсолютно всю номенклатуру и сопоставить её со всеми возможными дополнительными реквизитами, которые для нее настроены. При этом важно показать даже те реквизиты, которые в карточке конкретного товара не заполнены — на их месте должен отображаться NULL или пустое значение.

Сложность заключается в том, что в базе данных значения дополнительных реквизитов хранятся в табличной части ДополнительныеРеквизиты справочника Номенклатура. Если использовать обычное левое соединение справочника с его табличной частью, мы получим только те свойства, которые физически заполнены пользователями. Незаполненные свойства просто не попадут в выборку. Чтобы решить эту задачу, нам потребуется построить полную матрицу (декартово произведение) всех товаров и всех доступных для них свойств, а затем соединить её с реальными значениями.

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

Архитектурная суть проблемы

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

1. Справочник.Номенклатура — сам список товаров.
2. ПланВидовХарактеристик.ДополнительныеРеквизитыИСведения — глобальный список всех возможных свойств в системе.
3. Справочник.НаборыДополнительныхРеквизитовИСведений — справочник, который определяет, какие именно свойства из плана видов характеристик (ПВХ) привязаны к конкретному виду номенклатуры или справочнику в целом.
4. Табличная часть ДополнительныеРеквизиты внутри справочника Номенклатура — здесь хранятся пары "Свойство - Значение" для каждого конкретного товара.

Чтобы получить полную матрицу, нам необходимо:

1. Выбрать всю номенклатуру.
2. Выбрать все свойства, которые теоретически могут быть у этой номенклатуры (исходя из ее вида или общих настроек).
3. Соединить их методом ПОЛНОГО (или ВНУТРЕННЕГО по ИСТИНА) соединения, чтобы получить строки вида: Номенклатура - Свойство для каждой возможной комбинации.
4. Соединить полученную виртуальную матрицу с реальной табличной частью ДополнительныеРеквизиты с помощью ЛЕВОГО соединения по двум полям: Номенклатура и Свойство.

Решение 1: Базовый запрос декартова произведения

Рассмотрим базовый вариант запроса, который решает задачу «в лоб» для свойств, привязанных к общему набору номенклатуры. Этот запрос отлично подойдет, если у вас все свойства привязаны к одному общему набору параметров.

ВЫБРАТЬ
	ВложенныйЗапрос.Номенклатура КАК Номенклатура,
	ВложенныйЗапрос.Реквизит КАК Реквизит,
	ЗначРек.Значение КАК Значение
{ВЫБРАТЬ
	Номенклатура.*,
	Реквизит.*,
	Значение.*}
ИЗ
	(ВЫБРАТЬ
		СпрНом.Ссылка КАК Номенклатура,
		ДопРек.Ссылка КАК Реквизит
	ИЗ
		Справочник.Номенклатура КАК СпрНом
			ПОЛНОЕ СОЕДИНЕНИЕ ПланВидовХарактеристик.ДополнительныеРеквизитыИСведения КАК ДопРек
			ПО (ДопРек.НаборСвойств = ЗНАЧЕНИЕ(Справочник.НаборыДополнительныхРеквизитовИСведений.Справочник_Номенклатура))
	{ГДЕ
		СпрНом.Ссылка.* КАК Номенклатура,
		ДопРек.Ссылка.* КАК Реквизит}) КАК ВложенныйЗапрос
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК ЗначРек
		ПО ВложенныйЗапрос.Номенклатура = ЗначРек.Ссылка
			И ВложенныйЗапрос.Реквизит = ЗначРек.Свойство

Давайте разберем по шагам, как работает этот код:

1. Во вложенном запросе мы берем справочник Номенклатура и выполняем его ПОЛНОЕ СОЕДИНЕНИЕ с планом видов характеристик.
2. Условие соединения ПО (ДопРек.НаборСвойств = ЗНАЧЕНИЕ(Справочник.НаборыДополнительныхРеквизитовИСведений.Справочник_Номенклатура)) ограничивает список свойств только теми, которые относятся к общим реквизитам номенклатуры.
3. На выходе вложенного запроса мы имеем виртуальную таблицу, где для каждой позиции номенклатуры создана строка под каждый реквизит.
4. Затем эту виртуальную таблицу мы связываем через ЛЕВОЕ СОЕДИНЕНИЕ с реальной табличной частью ДополнительныеРеквизиты. Если у номенклатуры заполнено свойство, поле Значение заполнится реальными данными. Если свойство не заполнено, СУБД вернет NULL.

Решение 2: Усложненный запрос с учетом видов номенклатуры и общих свойств

На практике свойства редко привязываются к общему набору "Справочник_Номенклатура". Чаще всего в базах ERP и УТ 11 свойства жестко привязаны к конкретным Видам номенклатуры, а также существуют так называемые "Общие реквизиты", которые могут наследоваться. Чтобы учесть эту специфику, логику запроса нужно расширить.

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

ВЫБРАТЬ
	ДопСведенияИРеквизитыПоВидамНоменклатуры.ВидНоменклатуры КАК ВидНоменклатуры,
	ДопСведенияИРеквизитыПоВидамНоменклатуры.НаборСвойств КАК НаборСвойств,
	ДопСведенияИРеквизитыПоВидамНоменклатуры.Свойство КАК Свойство,
	НоменклатураБезГрупп.Ссылка КАК Номенклатура
ИЗ
	(ВЫБРАТЬ
		Номенклатура.Ссылка КАК Ссылка
	ИЗ
		Справочник.Номенклатура КАК Номенклатура
	ГДЕ
		Номенклатура.ЭтоГруппа = ЛОЖЬ) КАК НоменклатураБезГрупп
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			ВидыНоменклатуры.Ссылка КАК ВидНоменклатуры,
			НаборыДополнительныхРеквизитовИСведений.Ссылка КАК НаборСвойств,
			НаборыДополнительныхРеквизитовИСведений.Свойство КАК Свойство,
			ЛОЖЬ КАК ЭтоОбщееСвойствоСведение
		ИЗ
			Справочник.ВидыНоменклатуры КАК ВидыНоменклатуры
				ЛЕВОЕ СОЕДИНЕНИЕ Справочник.НаборыДополнительныхРеквизитовИСведений.ДополнительныеРеквизиты КАК НаборыДополнительныхРеквизитовИСведений
				ПО ВидыНоменклатуры.НаборСвойств = НаборыДополнительныхРеквизитовИСведений.Ссылка
		ГДЕ
			НаборыДополнительныхРеквизитовИСведений.Ссылка В ИЕРАРХИИ (ЗНАЧЕНИЕ(Справочник.НаборыДополнительныхРеквизитовИСведений.Справочник_Номенклатура))
			И НЕ НаборыДополнительныхРеквизитовИСведений.Ссылка.ПометкаУдаления
		
		ОБЪЕДИНИТЬ ВСЕ
		
		ВЫБРАТЬ
			НЕОПРЕДЕЛЕНО,
			СвойстваОбъектов.Ссылка,
			СвойстваОбъектов.Свойство,
			ИСТИНА
		ИЗ
			Справочник.НаборыДополнительныхРеквизитовИСведений.ДополнительныеРеквизиты КАК СвойстваОбъектов
		ГДЕ
			СвойстваОбъектов.Ссылка В ИЕРАРХИИ (ЗНАЧЕНИЕ(Справочник.НаборыДополнительныхРеквизитовИСведений.Справочник_Номенклатура))
			И НЕ СвойстваОбъектов.Ссылка.ПометкаУдаления
			И СвойстваОбъектов.Ссылка = &ПризнакОбщиеРеквизиты) КАК ДопСведенияИРеквизитыПоВидамНоменклатуры
		ПО (НоменклатураБезГрупп.Ссылка.ВидНоменклатуры = ДопСведенияИРеквизитыПоВидамНоменклатуры.ВидНоменклатуры
				ИЛИ ДопСведенияИРеквизитыПоВидамНоменклатуры.ЭтоОбщееСвойствоСведение)

Рассмотрим структуру этого сложного запроса детально:

1. Сначала мы отсекаем группы номенклатуры во вложенной таблице НоменклатураБезГрупп, так как группы не содержат дополнительных реквизитов.
2. Далее мы формируем сложную таблицу доступных свойств ДопСведенияИРеквизитыПоВидамНоменклатуры. Мы объединяем два запроса. Первый выбирает реквизиты, привязанные к конкретным видам номенклатуры. Второй запрос выбирает "Общие" реквизиты, которые привязаны к предопределенному элементу, переданному через параметр &ПризнакОбщиеРеквизиты.
3. Связывание происходит по условию: номенклатура должна соответствовать своему виду номенклатуры, либо свойство должно быть помечено флагом ЭтоОбщееСвойствоСведение.

Важный нюанс: Оптимизация производительности и проблема "Out of memory"

Внимание! Применение декартова произведения (умножения каждой строки номенклатуры на каждое свойство) таит в себе серьезную опасность для производительности системы. Если в вашей базе данных содержится 100 000 элементов номенклатуры, а количество активных дополнительных реквизитов равно 50, то размер результирующей виртуальной таблицы составит 5 000 000 строк.

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

• Используйте временные таблицы: Никогда не выполняйте сложные соединения "на лету". Сначала выгрузите во временную таблицу только ту номенклатуру, которая вам реально нужна для отчета (примените к ней все пользовательские фильтры, например, по группе, производителю или складу).
• Индексируйте временные таблицы: Перед соединением временной таблицы номенклатуры со свойствами обязательно создайте индекс по полям Ссылка и ВидНоменклатуры.
• Ограничивайте список свойств: Не выбирайте помеченные на удаление свойства. В запросах выше используется условие НЕ ПометкаУдаления — это обязательное требование для исключения "мусорных" колонок.
• Явно приводите типы (Выразить): Поле Значение в табличной части доп. реквизитов имеет составной тип. Если вы знаете, какой тип должен быть у конкретного реквизита, используйте конструкцию ВЫРАЗИТЬ(Значение КАК...). Это значительно снизит нагрузку на оптимизатор СУБД.

Решение 3: Использование механизма "Характеристики" в СКД (Best Practice)

Если ваша задача — вывести эти данные в отчет, созданный на Системе компоновки данных (СКД), то писать громоздкие запросы с полными соединениями вообще не нужно! В СКД встроен мощный штатный механизм работы с характеристиками.

Выясним, как настроить характеристики в СКД:

1. Откройте конструктор схемы компоновки данных и перейдите на вкладку "Наборы данных".
2. В правом верхнем углу окна наборов данных нажмите кнопку "Характеристики...".
3. В открывшемся окне добавьте новую строку и укажите параметры:
   • Путь к данным: укажите поле, содержащее номенклатуру (например, Ссылка).
   • Источник видов характеристик: выберите запрос или таблицу, которая возвращает список доступных свойств. Например, запрос к справочнику НаборыДополнительныхРеквизитовИСведений.ДополнительныеРеквизиты.
   • Поле ключа: Свойство (ссылка на ПВХ).
   • Поле имени: Наименование свойства (для заголовка колонки).
   • Источник значений характеристик: табличная часть ДополнительныеРеквизиты вашей номенклатуры.
   • Поле объекта: Ссылка (владелец ТЧ).
   • Поле вида характеристики: Свойство.
   • Поле значения: Значение.

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