1C directories are a specialized metadata tree object that serves to store static reference information. For example, in typical configurations you can see the following views: , Nomenclature, Employees, Fixed Assets, etc. Information in directories, as a rule, does not change often. Directories are subsequently used in almost all accounting objects as an accounting section or reference information.

Below we will look at setting up and designing a directory from the configurator using the “Nomenclature” directory as an example.

Basic Tab

The “Basic” tab specifies the name, synonym, object representation, and description of purpose.

“Directory Hierarchy” tab

Here the hierarchy of the directory is established.

Hierarchy in 1C 8.3 is of two types - “ groups and elements" And " elements". It differs in that in the first case, only a folder (group) can be a parent (folder), and in the second case, an element can also be a parent.

“Place groups on top” - the flag is responsible for displaying groups in list form.

Also in the settings you can limit the number of groups in the directory hierarchy using the appropriate setting.

Owners Tab

A directory can be subordinated to another directory. From the point of view of configuring 1C 8.3, this means that the “Owner” attribute becomes mandatory for the subordinate element. An example of such a connection between directories in standard configurations “Nomenclature - Units of Measurement”, “Counterparties - Contracts of Contractors”.

The directory owner can also be the following metadata objects: , .

Data Tab

Get 267 video lessons on 1C for free:

The most important tab from a programmer's point of view. It contains the directory details.

The directory has a set of standard details that are not edited by the 1C 8.2 programmer; a list of them can be seen by clicking the “Standard Details” button:

I will dwell on each in more detail:

• This group— an attribute with a Boolean type, indicating whether it is a group or an element. Available only in the hierarchical directory. Note, the value of this attribute cannot be changed in 1C: Enterprise mode.
• Code— props, type number or string (usually a string). A number assigned automatically by the system. Typically calculated as (previous code + 1). I recommend using the string type, because sorting numeric values ​​does not work as expected. Can be used as a directory representation in a list and in input fields. Typically used to search for an element when entering a string. If you need to remove the Code field, enter zero in the line length.
• Name— mandatory details, string type. The maximum line length is 150 characters. Can be used as a directory representation in a list and in input fields. Typically used to search for an element when entering a string. If you need to remove the Name field, enter zero in the line length.
• Parent— an attribute of the DirectoryLink type.<ИмяТекущегоСправочника>. Available only in the hierarchical directory. Points to the superior parent in the hierarchy. If the Element or Group is at the root of the directory, the value Directory is specified.<ИмяТекущегоСправочника>.EmptyLink.
• Owner— link to the owner element of the current directory element (group). Available only in the subordinate 1C directory.
• FlagDeletion— props with type Boolean. Responsible for displaying the “deletion mark” in the system. An element marked for deletion is considered unusable, but old document movements may remain on it.
• Link— field of string type. This attribute stores a unique object identifier - GUID. What we see in the system in a visual display called “link” is just a representation of the object. Cannot be changed.
• Predefined— boolean type, displays whether the element is predefined, more on that later. Cannot be changed.

The “Data” tab also indicates the representation of the directory in the system; before version 8.2.16, the representation could only be Code or Name. In recent versions of the platform (starting from 8.3), the view can be described independently in the manager module using the “ViewReceivingProcessing” handler.

Numbering tab

Here you can specify the settings of the directory regarding numbering. It is recommended to use autonumbering. Uniqueness control is a flag that helps, if necessary, to make the code unique. If, with the flag set, you try to write a directory element with a non-unique code, in 1C you will receive the message “The directory code has become non-unique.”

Code series - determines how to number the directory; you can enter the numbering of the directory by owner. For example, the counterparty “Horns and Hooves” will have its own numbering of contracts - “1, 2, 3”, etc.

Forms Tab

The forms for the directory are described here. If the configuration is launched in both normal and managed modes, then there will be two tabs with forms by default: “main” and “advanced” - different for the normal and managed applications.

This page has an important feature of the directory - ““. This is a very convenient function of 1C 8, which allows you, when filling out data in the input field, not to go into the directory, but to type its name, code, etc. and select the desired element from the drop-down list. It looks like this:

Other Tab

On the tab you can get quick access to the main modules of the directory - the object module and the manager module.

You can also define a list of predefined directory elements on the page. These are items that cannot be deleted in Enterprise Mode. Predefined elements can be accessed directly in the configurator by name, for example: Directories.Nomenclature.Service.

This tab also determines the blocking mode - automatic or controlled. Use of full-text search, as well as reference information about the directory, available in 1C: Enterprise mode.

This section shows examples of solving typical problems when working with hierarchical directories.

Obtaining elements of a hierarchical directory that are subordinate to a given group

To obtain subordinate elements of a hierarchical directory, the query language provides the IN HIERARCHY construct. Example of use IN HIERARCHY:

CHOOSE
Nomenclature.Code,
Nomenclature.PurchasePrice
FROM

In this example, all records of the Nomenclature directory located in the &Group group will be obtained, including itself, its subordinate groups and elements belonging to subordinate groups.

If we are only interested in elements and groups located directly in a given group, then we can obtain such elements by setting a condition on the Parent field. Example:

CHOOSE
Nomenclature.Code,
Nomenclature.Name AS Name,
Nomenclature.PurchasePrice
FROM
Directory.Nomenclature AS Nomenclature

WHERE
Nomenclature.Parent = &Group

This query will select groups and elements subordinate to the group with the &Group link.

Checking the presence of subordinate elements of a directory element

To check the presence of subordinate records of a directory element, you can use a query similar to the one presented:

In this example, the reference of the element for which you want to check for children is written to the Parent query parameter. After executing such a query, you need to check the result for emptiness. If the result is not empty, then there are subordinate records. Otherwise - no. Example:

If Request.Execute().Empty() Then
Report("No entries");
Otherwise
Report("Records available");
endIf;

Getting all parents of an element

The query language does not provide any special means for retrieving all parents of an element. You can use hierarchical totals to complete the task, but obtaining hierarchical totals is optimized for building totals for a large number of records, and is not entirely effective for obtaining the parents of a single element. To more efficiently retrieve all parent records of an element, it is recommended to loop through its parents in small portions. Example:

CurrentItemItem = ItemItem;

Request = New Request("SELECT
| Nomenclature.Parent,
| Nomenclature.Parent.Parent,
| Nomenclature.Parent.Parent.Parent,
| Nomenclature.Parent.Parent.Parent.Parent,
| Nomenclature.Parent.Parent.Parent.Parent.Parent
|FROM
| Directory.Nomenclature AS Nomenclature
|WHERE
| Nomenclature.Link = &CurrentNomenclatureElement";

While the Truth Cycle
Request.SetParameter("CurrentItemItem", CurrentItemItem);
Result = Query.Run();
If Result.Empty() Then
Abort;
endIf;
Selection = Result.Select();
Selection.Next();
For ColumnNumber = 0 By Result.Columns.Quantity() - 1 Loop
CurrentItemItem = Selection[ColumnNumber];
Abort;
Otherwise
Report(CurrentItemItem);
endIf;
EndCycle;

If CurrentItemItem = Directories.Nomenclature.EmptyLink() Then
Abort;
endIf;
EndCycle;

In this example, all parents for the link recorded in the ElementNomenclature variable are displayed in the service message window. In the cycle, 5 link parents are selected.

If the number of levels in the directory is limited and small, then it is possible to obtain all parents with one request without a loop.

Displaying a hierarchical directory in a report

To display a hierarchical directory in a report while preserving the hierarchy, you must use a query similar to the following:

CHOOSE
Nomenclature.Code,
Nomenclature.Name AS Name,
Nomenclature.PurchasePrice
FROM
Directory.Nomenclature AS Nomenclature
SORT BY
Name HIERARCHY

This query selects all records from the directory and arranges them in a hierarchy. The result will be ordered by name, taking into account the hierarchy.

In order for directory groups to be placed above the elements, it is necessary to replace the ORDER BY clause in this request with the following:

SORT BY
Nomenclature.This is Group HIERARCHY,
Name

The result will still be ordered hierarchically, but the groups will appear above the elements.

It is also possible to replace the ORDER BY offer with the AUTO ORDER option. In this case, the result will be ordered in accordance with the settings of the directory, i.e. if the directory states that groups should be located above the elements, then they will be located above.

It is also possible to obtain the hierarchical structure of the directory using the results.

CHOOSE
Nomenclature.Code,
Nomenclature.Name AS Name,
Nomenclature.PurchasePrice

FROM Directory.Nomenclature AS Nomenclature

WHERE
(Nomenclature.ThisGroup = FALSE)

ORDER BY Name

Getting totals by hierarchy

To obtain totals by hierarchy in a query, you must specify the keyword HIERARCHY in the SOFTWARE TOTAL clause after specifying the field by which the totals will be calculated. An example of a report "Item turnover" with obtaining totals by hierarchy:

CHOOSE

FROM

Nomenclature HIERARCHY

As a result of this request, totals will be calculated not only for each item, but also for the groups to which this or that item belongs.

In the case where we do not need totals for elements, but only need totals for groups, we need to use the HIERARCHY ONLY construction in the totals. Example:

CHOOSE
Accounting for NomenclatureTurnover.Nomenclature AS Nomenclature,
Accounting for NomenclatureTurnover.Nomenclature.Presentation,
Accounting for NomenclatureTurnover.QuantityTurnover AS QuantityTurnover
FROM
Accumulation Register.Nomenclature Accounting.Turnover HOW Nomenclature AccountingTurnover
RESULTS AMOUNT (QuantityTurnover) PO
Nomenclature HIERARCHY ONLY

The result of this query will be total records only for item groups.

The query language in 1C 8 is a simplified analogue of the well-known “structured programming language” (as it is more often called, SQL). But in 1C it is used only for reading data; an object data model is used to change data.

Another interesting difference is the Russian syntax. Although in fact you can use English-language constructions.

Example request:

CHOOSE
Banks.Name,
Banks.CorrespondentAccount
FROM
Directory.Banks HOW Banks

This request will allow us to see information about the name and correspondent account of all banks existing in the database.

Query language is the simplest and most effective way to obtain information. As can be seen from the example above, in the query language you need to use metadata names (this is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).

Description of query language constructs

Query structure

To obtain data, it is enough to use the “SELECT” and “FROM” constructions. The simplest request looks like this:

SELECT * FROM Directories.Nomenclature

Where “*” means selecting all fields of the table, and Directories.Nomenclature – the name of the table in the database.

Let's look at a more complex and general example:

CHOOSE
<ИмяПоля1>HOW<ПредставлениеПоля1>,
Sum(<ИмяПоля2>) HOW<ПредставлениеПоля2>
FROM
<ИмяТаблицы1>HOW<ПредставлениеТаблицы1>
<ТипСоединения>COMPOUND<ИмяТаблицы2>HOW<ПредставлениеТаблицы2>
BY<УсловиеСоединениеТаблиц>

WHERE
<УсловиеОтбораДанных>

GROUP BY
<ИмяПоля1>

SORT BY
<ИмяПоля1>

RESULTS
<ИмяПоля2>
BY
<ИмяПоля1>

In this query, we select the data of the fields “FieldName1” and “FieldName1” from the tables “TableName1” and “TableName”, assign synonyms to the fields using the “HOW” operator, and connect them using a certain condition “TableConnectionCondition”.

From the received data, we select only data that meets the condition from “WHERE” “Data Selection Condition”. Next, we group the request by the field “Field Name1”, while summing “Field Name2”. We create totals for the field “Field Name1” and the final field “Field Name2”.

The last step is to sort the request using the ORDER BY construct.

General designs

Let's look at the general structures of the 1C 8.2 query language.

FIRSTn

Using this operator, you can get the n number of first records. The order of the records is determined by the order in the query.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks
SORT BY
Banks.Name

The request will receive the first 100 entries of the “Banks” directory, sorted alphabetically.

ALLOWED

This design is relevant for working with the mechanism. The essence of the mechanism is to restrict reading (and other actions) to users for specific records in a database table, and not the table as a whole.

If a user tries to use a query to read records that are inaccessible to him, he will receive an error message. To avoid this, you should use the “ALLOWED” construction, i.e. the request will read only records that are allowed to it.

SELECT ALLOWED
Repository of Additional Information. Link
FROM
Directory.Repository of Additional Information

VARIOUS

Using “DIFFERENT” will prevent duplicate lines from entering the 1C query result. Duplication means that all request fields match.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks

EmptyTable

This construction is used very rarely to combine queries. When joining, you may need to specify an empty nested table in one of the tables. The “EmptyTable” operator is just right for this.

Example from 1C 8 help:

SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition
FROM Document.Expense Invoice
COMBINE EVERYTHING
SELECT Link.Number, Contents.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

ISNULL

A very useful feature that allows you to avoid many mistakes. YesNULL() allows you to replace the NULL value with the desired one. Very often used in checking for the presence of a value in joined tables, for example:

CHOOSE
Nomenclature Ref. Link,
IsNULL(Item Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM

Can be used in other ways. For example, if for each row it is not known in which table the value exists:

ISNULL(InvoiceReceived.Date, InvoiceIssued.Date)

HOW is an operator that allows us to assign a name (synonym) to a table or field. We saw an example of use above.

These constructions are very similar - they allow you to get a string representation of the desired value. The only difference is that REPRESENTATION converts any values ​​to a string type, while REPRESENTATIONREF converts only reference values. REFERENCE REPRESENTATION is recommended to be used in data composition system queries for optimization, unless, of course, the reference data field is planned to be used in selections.

CHOOSE
View(Link), //string, for example “Advance report No. 123 dated 10/10/2015
View(DeletionMark) AS DeleteMarkText, //string, “Yes” or “No”
ViewReferences(DeletionMark) AS DeleteMarkBoolean //boolean, True or False
FROM
Document.Advance Report

EXPRESS

Express allows you to convert field values ​​to the desired data type. You can convert a value to either a primitive type or a reference type.

Express for a reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS(TableCost.Subconto1 AS Directory.Cost Items).Type of ActivityForTaxAccountingCosts

For primitive types, this function is often used to limit the number of characters in fields of unlimited length (such fields cannot be compared with). To avoid the error " Invalid parameters in comparison operation. Cannot compare fields
unlimited length and fields of incompatible types", you need to express such fields as follows:

EXPRESS(Comment AS Line(150))

DIFFERENCEDATE

Get 267 video lessons on 1C for free:

An example of using IS NULL in a 1C request:

CHOOSE FROM
Ref
LEFT CONNECTION RegisterAccumulations.ProductsInWarehouses.Remaining AS Product Remaining
Software NomenclatureRef.Link = Sold GoodsCommitteesRemains.Nomenclature
WHERE NOT Remaining Products. QuantityRemaining IS NULL

The type of data in a query can be determined by using the TYPE() and VALUETYPE() functions, or by using the logical REFERENCE operator. The two functions are similar.

Predefined values

In addition to using passed parameters in queries in the 1C query language, you can use predefined values ​​or . For example, transfers, predefined directories, charts of accounts, and so on. For this, the “Value()” construct is used.

Usage example:

WHERE Nomenclature.Type of Nomenclature = Value(Directory.Types of Nomenclature.Product)

WHERE Counterparties.Type of Contact Information = Value(Enumeration.Types of Contact Information.Phone)

WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Profit.ProfitsLoss)

Connections

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

Joins are used to link two tables based on a specific condition. Feature when LEFT JOIN is that we take the first specified table in its entirety and conditionally bind the second table. The fields of the second table that could not be bound by condition are filled with the value NULL.

For example:

It will return the entire table of Counterparties and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” will be met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C language absolutely similar LEFT connection, with the exception of one difference - in RIGHT OF CONNECTION The “main” table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from left and right in that it displays all records from two tables and connects only those that it can connect by condition.

For example:

FROM

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the condition to join the records is met. Unlike a left/right join, it is possible for NULL to appear in two fields.

INNER JOIN

INNER JOIN differs from full in that it displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory.Banks HOW Banks

BY
Clients.Name = Banks.Name

This query will return only rows in which the bank and counterparty have the same name.

Associations

The JOIN and JOIN ALL constructs combine two results into one. Those. the result of performing two will be “merged” into one, common one.

That is, the system works exactly the same as regular ones, only for a temporary table.

How to use INDEX BY

However, one point should be taken into account. Building an index on a temporary table also takes time to complete. Therefore, it is advisable to use the “ ” construction only if it is known for sure that there will be more than 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance of indexed fields does not compensate for the time it takes to build the index.

CHOOSE
Currency rates Latest cross-section. Currency AS Currency,
Currency rates Latest cross-section.
PUT Currency Rates
FROM
Information Register.Currency Rates.Last Slice(&Period,) AS Currency RatesLast Slice
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclatures.Price,
PricesNomenclatures.Currency,
Currency rates.Rate
FROM
Information Register.Nomenclature Prices.Last Slice(&Period,
Nomenclature B (&Nomenclature) AND PriceType = &PriceType) AS PriceNomenclature
LEFT JOIN Currency Rates AS Currency Rates
Software PricesNomenclatures.Currency = Currency Rates.Currency

Grouping

The 1C query language allows you to use special aggregate functions when grouping query results. Grouping can also be used without aggregate functions to “eliminate” duplicates.

The following functions exist:

Amount, Quantity, Number of different, Maximum, Minimum, Average.

Example #1:

CHOOSE
Sales of Goods and Services Goods. Nomenclature,
SUM(Sales of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Sales of GoodsServicesGoods.Amount) AS Amount
FROM

GROUP BY
Sales of Goods and Services Goods. Nomenclature

The request receives all lines with goods and summarizes them by quantity and amounts by item.

Example No. 2

CHOOSE
Banks.Code,
QUANTITY(DIFFERENT Banks.Link) AS Number Of Duplicates
FROM
Directory.Banks HOW Banks
GROUP BY
Banks.Code

This example will display a list of BICs in the “Banks” directory and show how many duplicates exist for each of them.

Results

Results are a way to obtain data from a system with a hierarchical structure. Aggregate functions can be used for summary fields, just as for groupings.

One of the most popular ways to use results in practice is batch write-off of goods.

CHOOSE




FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
SORT BY

RESULTS
SUM(Quantity),
SUM(Sum)
BY
Nomenclature

The result of the query will be the following hierarchical:

General results

If you need to get totals for all “totals”, use the “GENERAL” operator.

CHOOSE
Sales of Goods and Services Goods. Nomenclature AS Nomenclature,
Sales of Goods and Services Goods. Link AS Document,
Sales of Goods and Services Goods. Quantity AS Quantity,
Sales of Goods and Services Goods. Amount AS Amount
FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
SORT BY
Sales of Goods and Services Goods. Link. Date
RESULTS
SUM(Quantity),
SUM(Sum)
BY
ARE COMMON,
Nomenclature

As a result of executing the request, we get the following result:

In which 1 level of grouping is the aggregation of all necessary fields.

Arranging

The ORDER BY operator is used to sort the result of a query.

Sorting for primitive types (string, number, boolean) follows the usual rules. For reference type fields, sorting occurs by the internal representation of the link (the unique identifier), rather than by code or by reference representation.

CHOOSE

FROM
Directory.Nomenclature AS Nomenclature
SORT BY
Name

The request will display a list of names in the nomenclature directory, sorted alphabetically.

Auto-order

The result of a query without sorting is a chaotically presented set of rows. 1C platform developers do not guarantee that rows will be output in the same sequence when executing identical queries.

If you need to display table records in a constant order, you must use the Auto-Order construct.

CHOOSE
Nomenclature.Name AS Name
FROM
Directory.Nomenclature AS Nomenclature
AUTO ORDER

Virtual tables

Virtual tables in 1C are a unique feature of the 1C query language that is not found in other similar syntaxes. A virtual table is a quick way to obtain profile information from registers.

Each register type has its own set of virtual tables, which may differ depending on the register settings.

• cut of the first;
• cut of the latter.

• leftovers;
• revolutions;
• balances and turnover.

• movements from subconto;
• revolutions;
• speed Dt Kt;
• leftovers;
• balances and turnover
• subconto.

• base;
• graph data;
• actual period of validity.

For the solution developer, the data is taken from one (virtual) table, but in fact the 1C platform takes from many tables, transforming them into the required form.

CHOOSE
Products in Warehouses Remains and Turnover. Nomenclature,
ProductsInWarehousesRemainingAndTurnover.QuantityInitialRemaining,
ProductsInWarehousesRemainsAndTurnover.QuantityTurnover,
GoodsInWarehousesRemainsAndTurnover.QuantityIncoming,
ProductsIn WarehousesRemainsAndTurnover.QuantityConsumption,
ProductsInWarehousesRemainingsAndTurnover.QuantityFinalRemaining
FROM
RegisterAccumulations.GoodsInWarehouses.RemainsAndTurnover AS GoodsInWarehousesRemainsAndTurnover

This query allows you to quickly retrieve a large amount of data.

Virtual Table Options

A very important aspect of working with virtual tables is the use of parameters. Virtual table parameters are specialized parameters for selection and configuration.

For such tables, it is considered incorrect to use selection in the “WHERE” construction. In addition to the fact that the query becomes suboptimal, it is possible to receive incorrect data.

An example of using these parameters:

Register of Accumulations. Goods in Warehouses. Balances and Turnovers (& Beginning of the Period, & End of the Period, Month, Movements and Borders of the Period, Nomenclature = & Required Nomenclature)

Algorithm for virtual tables

For example, the most used virtual table of the “Remains” type stores data from two physical tables – balances and movements.

When using a virtual table, the system performs the following manipulations:

1. We get the closest calculated value in terms of date and measurements in the totals table.
2. We “add” the amount from the movement table to the amount from the totals table.

Such simple actions can significantly improve the performance of the system as a whole.

Using the Query Builder

Query Builder– a tool built into the 1C Enterprise system that greatly facilitates the development of database queries.

The query builder has a fairly simple, intuitive interface. Nevertheless, let's look at using the query constructor in more detail.

The query text constructor is launched from the context menu (right mouse button) in the desired place in the program code.

Description of the 1C request constructor

Let's look at each tab of the designer in more detail. The exception is the Builder tab, which is a topic for another discussion.

Tables and Fields tab

This tab specifies the data source and fields that need to be displayed in the report. In essence, the constructions SELECT.. FROM are described here.

The source can be a physical database table, a virtual register table, temporary tables, nested queries, etc.

In the context menu of virtual tables, you can set virtual table parameters:

Connections tab

The tab is used to describe connections of several tables and creates constructions with the word CONNECTION.

Grouping tab

On this tab, the system allows you to group and summarize the required fields of the table result. Describes the use of the constructions GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, QUANTITY, NUMBER OF DIFFERENT.

Conditions tab

Responsible for everything that comes in the request text after the WHERE construction, i.e. for all the conditions imposed on the received data.

Advanced tab

Tab Additionally replete with all sorts of parameters that are very important. Let's look at each of the properties.

Grouping Selecting records:

• First N– a parameter that returns only N records to the query (the FIRST operator)
• No duplicates– ensures the uniqueness of the received records (DIFFERENT operator)
• Allowed– allows you to select only those records that the system allows you to select taking into account (ALLOWED construction)

Grouping Request type determines what type of query will be: retrieving data, creating a temporary table, or destroying a temporary table.

Below there is a flag Lock received data for later modification. It allows you to enable the ability to set data locking, which ensures the safety of data from the moment it is read until it is changed (relevant only for the Automatic locking mode, design FOR CHANGE).

Joins/Aliases Tab

On this tab of the query designer, you can set the ability to join different tables and aliases (the HOW construct). The tables are indicated on the left side. If you set the flags opposite the table, the UNITE construction will be used, otherwise - UNITE ALL (differences between the two methods). On the right side, the correspondence of fields in different tables is indicated; if the correspondence is not specified, the query will return NULL.

Order tab

This specifies the order in which the values ​​are sorted (ORDER BY) - descending (DESC) or ascending (ASC).

There is also an interesting flag - Auto-order(in the request - AUTO ORDERING). By default, the 1C system displays data in a “chaotic” order. If you set this flag, the system will sort data by internal data.

Query Batch tab

On the query designer tab, you can create new ones, and also use it as a navigation. In the request text, packets are separated by the symbol “;” (comma).

“Query” button in the query designer

In the lower left corner of the request designer there is a Request button, with which you can view the request text at any time:

In this window, you can make adjustments to the request and execute it.

Using the Query Console

The Query Console is a simple and convenient way to debug complex queries and quickly obtain information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C query console

First of all, to start working with the query console, you need to download it from somewhere. Treatments are usually divided into two types - controlled forms and conventional ones (or, sometimes, they are called 8.1 and 8.2/8.3).

I tried to combine these two views in one processing - the desired form opens in the desired operating mode (in managed mode, the console only works in thick mode).

Description of the 1C query console

Let's start looking at the query console with a description of the main processing panel:

In the header of the query console, you can see the execution time of the last query with millisecond accuracy, this allows you to compare different designs in terms of performance.

The first group of buttons in the command bar is responsible for saving current queries to an external file. This is very convenient; you can always return to writing a complex request. Or, for example, store a list of typical examples of certain designs.

On the left, in the “Request” field, you can create new requests and save them in a tree structure. The second group of buttons is responsible for managing the list of requests. Using it you can create, copy, delete, move a request.

• Executerequest– simple execution and results
• Execute package– allows you to view all intermediate queries in a batch of queries
• Viewing temporary tables– allows you to see the results that temporary queries return on a table

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

• Button Get from request automatically finds all parameters in the request for the convenience of the developer.
• Flag Common parameters for all requests– when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.

Set a parameter with a list of values It’s very simple, just when choosing a parameter value, click on the clear value button (cross), the system will prompt you to select the data type, where you need to select “Value List”:

Also in the top panel there is a button for calling up the query console settings:

Here you can specify parameters for autosaving queries and query execution parameters.

The request text is entered into the console request field. This can be done by simply typing a query test or by calling a special tool - the query designer.

The 1C 8 query designer is called from the context menu (right mouse button) when you click on the input field:

This menu also has such useful functions as clearing or adding line breaks (“|”) to the request, or receiving the request code in this convenient form:

Request = New Request;
Request.Text = ”
|SELECT
| Currencies.Link
|FROM
| Directory.Currencies AS Currencies”;
RequestResult = Request.Execute();

The lower field of the query console displays the query result field, which is why this processing was created:

Also, the query console, in addition to the list, can display data in the form of a tree - for queries containing totals.

Query optimization

One of the most important points in increasing the productivity of 1C enterprise 8.3 is optimizationrequests. This point is also very important when passing the certification. Below we will talk about typical reasons for non-optimal query performance and optimization methods.

Selections in a virtual table using the WHERE construct

It is necessary to apply filters to the virtual table details only through the VT parameters. Under no circumstances should you use the WHERE construct for selection in a virtual table; this is a serious mistake from an optimization point of view. In the case of selection using WHERE, in fact, the system will receive ALL records and only then select the necessary ones.

RIGHT:

CHOOSE

FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (
,
Organization = &Organization
AND Individual = &Individual) HOW Mutual settlements with Participants of Organizations Balances

WRONG:

CHOOSE
Mutual settlements with Participants of Organizations Balances. Amount Balance
FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (,) HOW Mutual settlements with Participants of Organizations Balances
WHERE
Mutual settlements with Participants of Organizations Balances. Organization = & Organization
AND Mutual settlements with Participants of Organizations Balances. Individual = &Individual

Getting the value of a field of a complex type using a dot

When receiving data of a complex type in a query through a dot, the system connects with a left join exactly as many tables as there are types possible in the field of the complex type.

For example, it is highly undesirable for optimization to access the register record field – registrar. The registrar has a composite data type, among which are all possible document types that can write data to the register.

WRONG:

CHOOSE
Record Set.Recorder.Date,
RecordSet.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS SetRecords

That is, in fact, such a query will access not one table, but 22 database tables (this register has 21 registrar types).

RIGHT:

CHOOSE
CHOICE
WHEN ProductsOrg.Registrar LINK Document.Sales of Products and Services
THEN EXPRESS(ProductsOrg.Registrar AS Document.Sale of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Receipt of GoodsServices).Date
END AS DATE,
ProductsOrg.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS ProductsOrganization

Or the second option is to add such information to the details, for example, in our case, adding a date.

RIGHT:

CHOOSE
ProductsOrganizations.Date,
ProductsOrganizations.Quantity
FROM
Register of Accumulations. Goods of Organizations AS Goods of Organizations

Subqueries in a join condition

For optimization, it is unacceptable to use subqueries in join conditions; this significantly slows down the query. It is advisable to use VT in such cases. To connect, you need to use only metadata and VT objects, having previously indexed them by connection fields.

WRONG:

CHOOSE …

LEFT JOIN (
SELECT FROM RegisterInformation.Limits
WHERE …
GROUP BY...
) BY …

RIGHT:

CHOOSE …
PUT Limits
FROM Information Register.Limits
WHERE …
GROUP BY...
INDEX BY...;

CHOOSE …
FROM Document. Sales of Goods and Services
LEFT JOIN Limits
BY …;

Joining Records with Virtual Tables

There are situations when, when connecting a virtual table to others, the system does not work optimally. In this case, to optimize the performance of the query, you can try placing the virtual table in a temporary table, not forgetting to index the joined fields in the temporary table query. This is due to the fact that VTs are often contained in several physical DBMS tables; as a result, a subquery is compiled to select them, and the problem turns out to be similar to the previous point.

Using selections based on non-indexed fields

One of the most common mistakes when writing queries is using conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot execute a query optimally if the query includes selection on non-indexable fields. If you take a temporary table, you also need to index the connection fields.

There must be a suitable index for each condition. A suitable index is one that satisfies the following requirements:

1. The index contains all the fields listed in the condition.
2. These fields are at the very beginning of the index.
3. These selections are consecutive, that is, values ​​that are not involved in the query condition are not “wedged” between them.

If the DBMS does not select the correct indexes, the entire table will be scanned - this will have a very negative impact on performance and can lead to prolonged blocking of the entire set of records.

Using logical OR in conditions

That's all, this article covered the basic aspects of query optimization that every 1C expert should know.

A very useful free video course on query development and optimization, I strongly recommend for beginners and more!

The “IN HIERARCHY” design in 1C:Enterprise 8.x queries allows you to obtain subordinate elements of a hierarchical configuration object according to a given selection. Today in the article we will look at an example of its use, as well as the actions of the platform on the DBMS side and its impact on performance.

Usage

Let's look at a simple example of using the "IN HIERARCHY" construction. When executing the following request, the subordinate elements of the hierarchical directory "Products" will be obtained for the passed value of the "Link" variable.

Query Text = " SELECT | Products . Link,| Goods |. vendor code FROM | Directory . Products AS Products"

|WHERE |

Goods

. Link IN HIERARCHY(& Link)

In the test database, the "Products" directory has the following test data:

Of course, the image does not show all directory entries. The screenshot shows only the data storage structure in the hierarchical directory. The directory table stores 10 top-level groups, each of which contains 5 nested groups with 200 elements each. Let's return to the test request. Let's pass the link to the group "Group - 1" to the "&Link" parameter (see screenshot above). Then the result of the query will look like this: As we can see, the request returned a link to the top group itself (passed as a parameter), as well as nested groups with the elements in them. Thus, using the “IN HIERARCHY” construct allows you to conveniently obtain hierarchically subordinated data.

Syntax of the 1C:Enterprise query language

classic SQL

1. We will pass the top-level group “Group 1” as the “&Link” parameter (as we did earlier).
2. In the parameter we will pass a link to the group "Group 1 - 1", nested in the top-level group "Group 1".

Now, in order. In the first case, the platform will perform the following actions on the SQL server:

1. First, an SQL query is executed to obtain a link to the directory group passed as a parameter and all its subordinate groups. The result is placed in the temporary table "#tt1".

2. In the second stage, the same query is executed twice:

The screenshot contains detailed comments on the text of the SQL query. In short, the query allows you to select subordinate elements for groups that are referenced in a temporary table. The question remains: "Why is the query executed twice?" The answer here is simple: first, the query gets the subordinate elements for first-level groups that are already contained in the temporary table (see point 1). The second query then retrieves the subelements for the second-level subgroups. Since no directory group is present at the third level of the hierarchy, this query is no longer executed.

In our case, the second query will return an empty result, since there are no subordinate elements for records located at the 3rd level of the hierarchy (there are no groups there).

3. To obtain the final result of the query, the platform generates the following SQL query:

The result of this particular request can be further processed by algorithms in the built-in language of the platform. Thus, entries in the temporary table "#tt1" are used to set the sampling condition from the reference table "_Reference41".

4. At the last step, the 1C:Enterprise 8.x platform deletes the temporary table "#tt1", since it will no longer be used in the future.

This completes the process of executing the “IN HIERARCHY” operator. Let me remind you that the considered sequence of actions on the SQL server was performed when we passed a link to the top-level group “Group - 1” to a request on the platform side. But how will the platform behave if we pass a link to the second-level group “Group - 1 - 1” as the “&Link” parameter? Everything will happen in the same way, except for the following point: above, in the second stage of executing SQL queries by the platform, it was written that the query to obtain subordinate elements was executed twice - in the case of obtaining subordinate elements for the group "Group - 1 - 1" this is not the case . The request will only be executed once.

The fact is that the number of requests to obtain subordinate elements depends on the number of groups in the hierarchy. In other words, if the element hierarchy level contains at least one group, then the request from point 2.

Performance Impact

Incorrect use of any operator in a query may result in suboptimal system performance. The operator under consideration “IN HIERARCHY” is no exception. It must be used with caution, since it greatly complicates the algorithm for executing SQL queries to the database and thereby increases the load on the DBMS server.

Let me give you an example of a suboptimal query that can lead to the sad consequences mentioned above:

SELECT Products. Link FROM Directory. Products AS Products WHERE (Products. Link IN HIERARCHY (& Link) OR Products. Link IN HIERARCHY (& Link1) OR Products. Link IN HIERARCHY (& Link2) )

As you might guess, the request will lead to the generation of many SQL queries, which will result in a decrease in the performance of the information system.

Draw your conclusions!

It's up to you to draw conclusions. Let me just say that the operator “IN HIERARCHY” is used by the platform for the data composition system when the selection conditions include “IN GROUP”, “IN GROUP FROM THE LIST” and others. I think there is no need to explain that with incorrect manipulations, users can set up very complex selections and increase the load on the 1C server and DBMS several times. Let's change the settings only for experienced users.

And of course, when writing your own mechanisms, pay attention to the “IN HIERARCHY” operator. Very convenient on the one hand, and dangerous on the other.

Attention! This is an introductory version of the lesson, the materials of which may be incomplete.

Login to the site as a student

Log in as a student to access school materials

Query language 1C 8.3 for beginner programmers: operators BETWEEN and IN

Logical operator BETWEEN

Operator BETWEEN allows you to check is the value included? expression specified to the left of it into the range specified to the right ( along with range boundaries, that is, inclusive).

So instead of

If, on the contrary, it is necessary to select all food, the calorie content of which is not included in the range, then the following form of negation is suitable (a particle has appeared NOT):

Operator BETWEEN Can be applied to more than just numeric ranges. It also works well with dates:

Logical operator B

Checking for a match with one of the listed

Operator IN allows you to check does the value match the expression indicated to the left of it, with one of the values described on the right.

So instead of

Can you write something more concise?

And the result will be the same:

If, on the contrary, it is necessary to select all food, the color of which does not coincide with any value from the list, then the following form of negation is suitable (a particle has appeared NOT):

Checking if a value matches one of the query results

Suppose we need to select from the database only those colors that are present in the description of the food. Thus, the selected list should not contain, for example, the color black, since there is no black food in our database. You are reading a trial version of the lesson, full lessons are available.

One way to do this is to use the logical operator IN, to the right of which there will be a subquery selecting color names from all directory entries Food:

As an aside, I’ll mention that from an internal request it is possible to access the fields of an external request.

Again, for this form of the operator IN, particle usage is also available NOT in front of him.

Checking hierarchy membership for directories

For directories, checking can also be carried out for hierarchy membership.

First, let's look at an example of a hierarchical directory. Open the "Cities" directory in our database:

Please note that its elements are different from other directories ( Food, Colors, Flavors) presence of yellow folders. This directory groups.

Groups differ from ordinary elements in that they can include other groups and elements. Just like folders contain other folders and files.

To view the contents of a group, double-click on it:

To go up a level, double-click on the group again:

Thus, a hierarchical directory can contain both ordinary elements (for example, Rio de Janeiro, Salvador) and groups (for example, Brazil, India). You are reading a trial version of the lesson, full lessons are available.

Each element (whether a group or an ordinary element) can have a parent. For example, the parent of the element Rio de Janeiro is the group Brazil:

And this is true because Rio de Janeiro is part of the Brazil group in the directory hierarchy:

Now let's write a query that will request the selected country group and all the city elements included in it.

Please note that in the request text there is an ampersand (&) before the name GroupCountry. Names with an ampersand are automatically recognized by the system as parameters whose value must be set before the query is executed.

After we paste this query into the console and click the Run button to update, we will be able to set this parameter:

Select the "Russia" group as its value (Select button):

If we now click the "Run" button, the result of the query will be as follows:

The result of the request was the group itself (Russia) and all the elements that are part of it (Perm, Krasnoyarsk and Voronezh).

If you select “Brazil” instead of Russia, the result will be like this:

Thus the result of the operator IN THE HIERARCHY will be TRUE if the value of the expression on the left is a reference to a directory element and is included in the set of values ​​on the right (Brazil) or hierarchically belongs to some group contained in this set (Sao Paulo, Rio de Janeiro, Salvador).

The result of a query can also be a set of values ​​against which a match is checked. In this case, to the right of the operator IN you must specify a description of the request:

For the operator IN THE HIERARCHY particle usage is also available NOT in front of him.

Take the test

Start test

1. The logical operator BETWEEN checks the value

2. Logical expression 1 BETWEEN 1 AND 1

3. Logical expression 1 NOT BETWEEN 2 AND 2

4. Logical operator B checks

5. Logical operator B allows you to check