Конструктор запросов 1С — обучение на примерах

Урок 3. Настройка связей таблиц в конструкторе запросов

Содержание статьи:

Таблицы и поля; Условия; Связи; Объединения / Псевдонимы; Группировка; Виртуальные таблицы.

Задача №1: выбрать запросом все проведенные документы Реализация товаров и услуг, за указанный период, у которых не создана счет-фактура.

Пояснение: конфигурациях Бухгалтерия 2.0 / 3.0 в реализации не хранится ссылка на выставленную счет-фактуру. Связь этих документов обратная: в счет-фактуре (в реквизите ДокументОснование) хранится ссылка на реализацию к которой она относится. Таким образом чтобы понять у каких реализаций нет счет-фактур, потребуется использовать в запросе две таблицы: РеализацияТоваровУслуг и СчетФактураВыданный.

Задача №2: допустим в базе у справочника Договоры контрагентов создано дополнительное сведение Отсрочка (место хранения регистр Дополнительные сведения). Выбрать запросом все договоры указанного контрагента с отсрочкой от 0 до 10 дней.

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

Новые вкладки: Связи.

Новые механизмы: проверка на null в запросе 1с.

Теоретическая часть урока №3

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

Каждая конкретная связь настраивается между двумя таблицами, одна из них называется Левая, другая Правая. Связи бывают трех типов:

• ВНУТРЕННЕЕ СОЕДИНЕНИЕ — запись из левой таблицы попадет в результат запроса только при выполнении условия связи, запись из правой таблицы попадет в результат запроса только при выполнении условия связи;
• ЛЕВОЕ СОЕДИНЕНИЕ — запись из левой таблицы попадет в результат запроса в любом случае, запись из правой таблицы попадет в выборку только при выполнении условия связи. Аналогом левого является ПРАВОЕ СОЕДИНЕНИЕ, данные правой таблицы попадают в результат в любом случае, данные левой таблицы только при выполнении условия. Обычно при написании запроса используется Левое соединение, если же вы используете правое, при следующем открытии запроса конструктор запросов преобразует его в левое поменяв таблицы местами;
• ПОЛНОЕ СОЕДИНЕНИЕ — запись из левой таблицы попадет в результат запроса сначала в любом случае, потом только при выполнении условия связи, запись из правой таблицы попадет в результат запроса сначала в любом случае, потом только при выполнении условия связи. При этом получившиеся в результате связи повторяющиеся строки исключаются из выборки.

В теории соединение таблиц запроса довольно сложно понять для неподготовленного программиста 1с, но при изучении примеров все непонятные моменты прояснятся.

Вкладка Связи в конструкторе запросов

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

После добавления новой связи необходимо заполнить все колонки таблицы. Разберем каждую колонку таблицы связей:

• Таблица 1. В данную колонку выбирается левая таблица связи. Можно использовать только таблицы выбранные на вкладке Таблицы и поля;
• Все. Флаг в данной колонке ставится в случае, если вам нужно выбрать все данные для левой таблицы (для Левого или Полного соединений);
• Таблица 2. В данную колонку выбирается правая таблица связи;
• Все. Флаг в данной колонке ставится в случае, если вам нужно выбрать все данные для правой таблицы (для Правого или Полного соединений);
• Произвольное. Флаг при помощи которого можно перейти в ручной режим редактирования условия связи;
• Условие связи. Условие на языке запросов, аналогичное условиям рассмотренным в Уроке №2. Представляет из себя выражение, которое всегда должно возвращать одно из двух значений: Истина или Ложь. Обычно условие связи состоит из трех частей:
  • Левая часть. Обычно поле левой таблицы, но это также может быть и полем правой таблицы или параметром (только в произвольном режиме редактирования условия);
  • Оператор сравнения. По умолчанию доступны «=», «<>», «<«, «>», «>=», «<=». Но в произвольном режиме редактирования можно использовать и некоторые другие, например «Между»;
  • Правая часть. Обычно поле правой таблицы, но это также может быть и полем левой таблицы или параметром (только в произвольном режиме редактирования условия);

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

Практическая часть урока №3

Разберем решение задач, приведенных в начале урока.

Задача №1

Выбрать запросом все проведенные документы Реализация товаров и услуг, за указанный период, у которых не создана счет-фактура.

• Создадим новый запрос;
• Запустим конструктор запросов;
• Выберем таблицы РеализацияТоваровУслуг и СчетФактураВыданный из ветки Документы;
• Из таблицы РеализацияТоваровУслуг выберем поле Ссылка;
• Перейдем на вкладку Условия;
• В разделе Поля, раскроем ветку РеализацияТоваровУслуг при помощи кнопки «+»;
• Найдем реквизит Дата и перетащим его в раздел условия, выберем оператор сравнения Между и укажем параметры начала и окончания периода, например НачалоПериода и ОкончаниеПериода;
• Из раздела Поля перетащим реквизит Проведен, поставим в строке с условием флаг Произвольное и сотрем лишнюю часть условия «= &Проведен»;
• Перейдем на вкладку Связи;
• Создадим новую связь при помощи кнопки «Добавить»;
• В поле Таблица 1 выберем таблицу РеализацияТоваровУслуг (это будет левая таблица);
• Для корректного решения задачи нам необходимо получить все реализации, присоединить к ним счет-фактуры и если счет-фактура не найдена ,вывести реализацию в результат запроса. Разобьем эту задачу на две подзадачи:
  • выберем все реализации и при помощи связи попытаемся найти их счет фактуры;
  • При помощи условия отберем только те строки, где нет счет-фактуры;
• Из предыдущего пункта следует, что нам необходимо использовать Левое соединение, для того чтобы выбрать все реализации. Поэтому поставим флаг в колонке Все, относящейся к левой таблице;
• В поле Таблица 2 выберем таблицу СчетФактураВыданный (это будет правая таблица);
• Так как ссылка на реализацию хранится в реквизите счет-фактуры ДокументОснование, в условии связи выберем:
  • В левой части поле РеализацияТоваровУслуг.Ссылка;
  • Оставим стандартный оператор сравнения «=»;
  • В правой части поле: СчетФактураВыданный.ДокументОснование.

• Настройка связей закончена;

При левом соединении, если для строки левой таблицы не нашлось ни одной строки правой таблицы удовлетворяющей условиям связи, все поля правой правой таблицы для данной строки левой таблицы имеют значение NULL. Воспользуемся этим для того, чтобы отобрать только те строки левой таблицы, для которых нет счет-фактуры;

В языке запросов 1с есть специальный логический оператор, позволяющий проверить содержится ли в поле значение NULL, его синтаксис <Поле> ЕСТЬ NULL. Он возвращает значение Истина, если поле имеет значение NULL и Ложь — в обратно случае.

Замечу, что проверить значение поля на NULL можно только при помощи указанной функции, конструкцией <Поле> = NULL осуществить данную проверку не получится.

• Перейдем на вкладку Условия;
• Перетащи в раздел условий одно из полей таблицы СчетФактураВыданный, например поле Ссылка;
• Установим в строке флаг Произвольное и допишем после поля оператор ЕСТЬ NULL, предварительно стерев ненужную часть условия;

В итоге у нас получится запрос со следующим текстом:

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("НачалоПерида", НачалоПерида);
Запрос.УстановитьПараметр("ОкончаниеПериода", ОкончаниеПериода);

Запрос.Текст = 
"ВЫБРАТЬ
|    РеализацияТоваровУслуг.Ссылка
|ИЗ
|    Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
|    ЛЕВОЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК СчетФактураВыданный
|        ПО РеализацияТоваровУслуг.Ссылка = СчетФактураВыданный.ДокументОснование
|ГДЕ
|    РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПерида И &ОкончаниеПериода
|    И РеализацияТоваровУслуг.Проведен
|    И СчетФактураВыданный.Ссылка ЕСТЬ NULL";

Задача №2

• Создадим новый запрос;
• Запустим конструктор запросов;
• На вкладке Таблицы и поля выберем две таблицы: ДоговорыКонтрагентов из ветки Справочники и ДополнительныеСведения из ветки РегистрыСведений;
• Из таблицы ДоговорыКонтрагентов выберем поле Ссылка;

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

• Перейдем на вкладку Условия. Из таблицы ДоговорыКонтрагентов перенесем в раздел условий поле Владелец. В правой части условия зададим имя параметра в который будем передавать нужного нам контрагента, назовем его Контрагент;
• Задачу по выборке договоров с отсрочками от 0 до 10, также разобьем на две подзадачи:
  • выберем все договоры и при помощи связей попытаемся найти их отсрочки;
  • при помощи условия выберем только договоры с нужными нам отсрочками;
• Настроим связи между договорами и дополнительными сведениями. Перейдем на вкладку Связи, и добавим новую связь;
• В поле Таблица 1 выберем таблицу ДоговорыКонтрагентов (это будет левая таблица);
• Так как нам нужны все договоры, будем использовать Левое соединение. Поставим флаг в колонке Все, относящейся к левой таблице;
• В поле Таблица 2 выберем таблицу ДополнительныеСведения (это будет правая таблица);
• Так как ссылка на договор хранится в измерении Объект регистра сведений Дополнительные сведения, в условии связи выберем:
  • В левой части поле ДоговорыКонтрагентов .Ссылка;
  • Оставим стандартный оператор сравнения «=»;
  • В правой части поле: ДополнительныеСведения.Объект.

Мы добавили связь, при помощи которой мы выберем все доп. сведения каждого договора. Но так как нам нужно только одно доп. сведение — Отсрочка, нам необходимо добавить еще одну связь. В данном случае Отсрочка  — это дополнительное свойство. Дополнительные свойства имеют тип План видов характеристик ДополнительныеРеквизитыИСведения. В регистре ДополнительныеСведения значение доп. свойства хранится в измерении Свойство. Таким образом нам необходимо добавить связь ограничивающую дополнительные сведения только свойством Отсрочка.

• Добавим новую связь;
• В поле Таблица 1 выберем таблицу ДоговорыКонтрагентов;
• Важно знать, что если у вас есть несколько связей на одну и ту же пару таблиц, то для них необходимо использовать один тип соединения. В нашем случае — это Левое соединение. Поэтому поставим флаг в колонке Все, относящейся к левой таблице;
• В поле Таблица 2 выберем таблицу ДополнительныеСведения;
• В условии связи нам не нужно будет использовать поле левой таблицы, вместо этого мы наложим условие на измерение Свойство, приравняв его к доп. свойству Отсрочка, которое будет передано в запрос при помощи параметра;
• Установим флаг Произвольное в значение Истина и напишем вручную следующий текст условия: «ДополнительныеСведения.Свойство = &СвойствоОтсрочка»;

• Настройка связей закончена;

Теперь осталось наложить условие на размер отсрочки. Так как у нас используется интервал от 0 до 10 необходимо будет использовать оператор сравнения Между. Так как нулевая отсрочка не хранится в дополнительных сведениях, при соединении в данном случае все поля регистра будут возвращать значение NULL. Для того чтобы в условии подменить значение NULL на 0, будем использовать функцию языка запросов ЕСТЬNULL(<Выражение1>, <Выражение2>). Функция возвращает Выражение1, если оно не равно NULL и Выражение2 в обратном случае.

• Перейдем на вкладку Условия конструктора запросов и добавим новое условие;
• Установим в нем флаг Произвольное и перейдем в редактор произвольных выражений;
• В разделе функций языка запросов раскроем ветку Функции -> Прочие функции;
• Перетащим в раздел выражений функцию ЕСТЬNULL;
• Вместо Выражения 1 вставим поле ДополнительныеСведения.Значение, а вместо Выражения 2 — 0;
• После получившейся конструкции впишем оператор сравнения Между и зададим интервал: 0 и 10;
• Нажмем ОК, условие готово;

Последнее, что необходимо сделать — вывести значение отсрочки в поля запроса. Для того чтобы вместо NULL выводилоcь значение 0, также используем редактор произвольных выражений и функцию ЕСТЬNULL.

• Перейдем на вкладку Таблицы и поля и добавим новое поле;
• В открывшемся редакторе произвольных выражений выберем функцию ЕСТЬNULL;
• Вместо Выражения 1 вставим поле ДополнительныеСведения.Значение, а вместо Выражения 2 — 0;
• Нажмем ОК, поле готово;

В итоге у нас получится запрос со следующим текстом:

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("СвойствоОтсрочка", СвойствоОтсрочка);
Запрос.УстановитьПараметр("Контрагент", Контрагент);

Запрос.Текст = 
"ВЫБРАТЬ
|    ДоговорыКонтрагентов.Ссылка,
|    ЕСТЬNULL(ДополнительныеСведения.Значение, 0) КАК Поле1
|ИЗ
|    Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
|    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК ДополнительныеСведения
|        ПО ДоговорыКонтрагентов.Ссылка = ДополнительныеСведения.Объект
|        И (ДополнительныеСведения.Свойство = &СвойствоОтсрочка)
|ГДЕ
|    ДоговорыКонтрагентов.Владелец = &Контрагент
|    И ЕСТЬNULL(ДополнительныеСведения.Значение, 0) МЕЖДУ 0 И 10";

Также прочтите статьи о языке запросов 1с 8:

• Язык запросов 1с — структура оператора Выбрать
• Программная работа с запросами 1с 8

<< Пред. страница   |   След.Страница >>