Being in a group according to the 1C 8 hierarchy. The “in hierarchy” operator in the request. Getting all parents of an element

12.12.2023Heading: Safety

Ildarovich 6489 16.11.12 18:24 Currently on topic

() Vladimir! I’m glad that you paid attention to the article, especially since you were one of the first to see (and appreciate) this technique in the discussion two years ago “It’s realistic to write a tricky query.” I didn’t come up with an interesting question on my own, but saw it on the forum. The author of the question is Stanislav Sheptalov. Next - on 10/24/12 the same (just noticed this, since the nickname is different) forum participant asked a similar question, but in application to the hierarchy. It turns out that the PRACTICAL issue has been resolved. Further, in accordance with the “scientific” approach, I looked at practical problems where this technique could be applied. Found 7 more problems. 5 - in this article. Among them is the problem about cycles in specifications, which I previously promised to solve Ish_2 with one query. I think Ish_2 will be able to convince you of the relevance of this task - he spent a lot of time on it. The solution is short - a few lines, therefore extremely clear, formulated in a non-procedural style, through a requirement for the result. Well, other problems were encountered in articles and on the forum, and more cumbersome solutions were proposed for them. So let's wait a while to see how often this will be applied. This is exactly the kind of feedback I expect - from those who will try.
By the way, the fact that this branch of mathematics is not far from practice and is needed by accountants is evidenced by the general module “Cost Adjustment” in BP2, which we are currently tinkering with (unstable operation of the standard request). There we are talking about breaking the cycles of the item movement graph and building a spanning tree.
Now about the structure of the database “for a specific task”. The question was asked about the implementation of the task in 1C and, therefore, the task was solved in 1C. If you were asked “which bus can you take to get to the library,” and you answered that it is better to fly on an airship, then you simply would not be understood (maybe except for those who are stuck in a Moscow traffic jam). Initially the method worked in a completely different language.
In general, I will not be able to convince you if you think that the architecture of the 1C platform is no good. I can only express my opinion. Developing a database schema from scratch for a specific task is expensive. If we compare it with construction: 1C is panel high-rise buildings - cheap housing - a means of mass automation - in cramped conditions, but no offense. Individual organizations can hire Norman Foster to accurately implement their requirements. The rest have to use cheap mass-produced projects - relational DBMSs with a rigid object model. In addition, I am familiar with the sad experience of using Cashe in several projects. Through the eyes of a developer, everything doesn’t look as rosy as it does in theory. The 1C object model stands the test of time - “vast territories are built up and inhabited.” Moreover, it is developing. Recently, the technology of external data sources has emerged. And if some task requires higher reactivity (for example, billing systems), you can now seamlessly connect 1C with another DBMS. For example, this is how we exchange with imported ERP.
But still, I would not like to divert the conversation from the main topic - the work of the proposed techniques in detailed PRACTICAL tasks.

What is a 1C directory and why is it needed? The directory stores conditionally permanent information, i.e. information that remains almost unchanged over a long period of time. For example, the “Nomenclature” directory contains a list of goods sold or produced. Also, a directory can contain many properties describing a directory element.

If we take the gender of a person for comparison, then the list is limited and not changed, so an enumeration is better suited for it.

Having created a new directory, we will see the following picture.

Let's look at all his bookmarks.

Basic

Here the name (identifier in the database) and synonym (user name of the directory) are indicated. An optional comment is one that can explain the purpose of the directory or describe its features.

Hierarchy

On this tab you can configure the depth of nesting of directory elements. Using this setting, it is convenient to differentiate and detail elements according to some criteria. For example, the products “Cabinets” are in one group, and the products “Tables” are in another. By default, when created, the directory presents list of elements. If you check the Hierarchical directory checkbox, then each element can be subordinate to another element (group). Below are options for customizing this bookmark and changing the display in custom mode.

Hierarchy type:

Hierarchy of groups and elements

With this setting, elements can only be nested in groups (folders).

Here, as you can see, all elements and groups have the same icons, and any element can be nested.

Place groups on top

When this checkbox is checked, the groups will always be at the top, otherwise they will be arranged in sort order, for example, like this:

Limiting the number of hierarchy levels

If the checkbox is not checked here, then nesting is unlimited.

If the checkbox is checked, you can specify the number of levels below.

Owners

On the bookmark owners other directories may be indicated in relation to which this one is subordinate. The relationship diagram of subordinate directories is similar to the relationship diagram of a hierarchical directory, only here another directory acts as the parent and is called the owner. In typical configurations, a good example is the subordination of the "Agreements" directory to the "Counterparties" directory, because There cannot be an agreement that does not belong to any counterparty.

The "List of Directory Owners" field specifies the list of directories that own the elements of this directory.

Below in the “Use of subordination” field it is indicated what the elements of this directory will be subordinated to.

How to programmatically find out whether a directory is hierarchical or not

To do this you need to refer to the metadata

This is HierarchicalDirectory = Metadata.Directories.Counterparties.Hierarchical;

To be continued...

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 presentation 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 presentation 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 an 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.

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.

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, the use of the “IN HIERARCHY” construction allows you to conveniently obtain hierarchically subordinated data.

Syntax of the 1C:Enterprise query language classic SQL very similar in some respects. But for the expression “IN HIERARCHY” there is no analogue in the SQL query language as, for example, for the expression of the platform “B” query language there is a similar SQL operator “IN”. Therefore, the work of the platform with the DBMS when using this operator is interesting.

Behind the scenes

So let's get started. For example, we will use the previously written query for the “Products” directory. We will analyze the actions of the platform for two situations:

We will pass the top-level group “Group 1” as the “&Link” parameter (as we did earlier).
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 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 receives 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.

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 to 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;

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 by 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.