The list is an integral attribute of displaying information in any configuration created on the 1C:Enterprise 8.1 platform. To effectively work with lists containing a variety of data, programs on the 1C:Enterprise 8.1 platform implement convenient and powerful selection and sorting mechanisms. V.V. talks about the features of their use. Fishing, company "1C".

Rice. 1

Lists

What lists do users encounter? These could be lists of documents in relevant journals. Almost any directory filled with data is presented in the form of a list, which, as a rule, has a hierarchy (folder groups or subordinate elements). Often, to select an element, the program generates lists automatically or in accordance with the logic laid down by the developers.

In almost all lists, you can use selection mechanisms (unless explicitly prohibited by the developer) and diverse sorting (Fig. 1). In the program, these mechanisms are inextricably linked with each other. Let's look at how to use them correctly and effectively.

Rice. 1

Sorting mechanism

Why do we need a sorting mechanism? Firstly, as the name suggests, it allows you to sort the list according to the desired criterion.

Secondly, the 1C:Enterprise 8.1 platform actively uses the quick search mechanism. This mechanism allows you to quickly find the desired element in any list by typing the first characters (Fig. 2). But in dynamic lists (for example, a list of directory items or a list of documents), quick search only works by details by which sorting is available.

Rice. 2

You can view all the list details available for sorting by opening the “Selection and Sorting” window. This window is usually available on the action bar (Fig. 3) or in the list context menu that opens with the right mouse button (Fig. 4). In the “Selection and Sorting” window, you need to go to the “Sorting” tab, which shows all the available details by which sorting is possible.

Rice. 3

Rice. 4

Figure 3 shows that for the list of documents “Sales of goods and services”, sorting by two details is available: “Date” and “Number”. However, the information content of the sorting window is not limited to this. Here you can see which sorting is currently enabled (left side of the window) and a list of all details available for sorting (right side). You can also configure the desired sorting and its order. Please note: the current sorting attribute will be marked in the list with a special symbol - an arrow indicating the sorting direction. Three examples of different sorting (including combined) and ways of displaying it in the list are shown in Figure 5.

Rice. 5

Note that the lower example illustrates the possibility of using combined sorting (simultaneously by several details). In our case, this is sorting by date in descending order (oldest dates at the bottom) and by number in ascending order (largest numbers at the bottom).

In each list you can individually save the desired sorting. Just check the box "Use this sorting setting when opening" and click "OK". The system will remember the setting and the next time you open this list, it will set the saved sorting.

You can quickly sort the list by the required attribute by clicking on the header with the name of this attribute.

For example, in our case, you can click on the “Date” attribute (the sorting of dates in ascending order will be switched on, and you can use a quick search by date) or on the “Number” attribute (the sorting by numbers in ascending order will be switched on, after which you can quickly search by number). Inverting the sorting is also easy - just click again on the same attribute in the list header.

Selection mechanism

Selection is a powerful mechanism of the 1C:Enterprise 8.1 platform, which allows you to effectively work with lists, even if they contain dozens or hundreds of thousands of elements. First, let's look at how the selection mechanism works in the general case.

Almost wherever there is a list (usually in the form of a table), you can use a selection mechanism, which is activated either by the corresponding button on the action bar or in the context-sensitive menu of the list (Fig. 3 and 4). If selection is possible for the desired list, the “Selection and sorting” window will open. It will display all possible types of details available for selection. The list of available selection elements depends on where the selection will be used. In general, you need to find one or more required elements for selection, specify the values ​​and activate the configured combination of criteria.

The list of possible conditions for a particular type of selection depends on the type of elements used in it. For example, ranges will be available for numbers and dates, substring search will be available for strings (conditions "Contains"/"Does not contain"), and list filling and hierarchy analysis will be available for directory elements.

If you specify multiple selection criteria at the same time, only those items that meet all of the specified criteria will be displayed in the list.

Selection and sorting mechanisms with examples

Let's try to solve several problems in the demo database ("Enterprise Accounting", edition 1.6). For example, let's display documents generated for the counterparty "Simon and Schuster LLC" in the "Customer Documents" journal. Let’s immediately make a reservation that we will describe the rules and selection logic standard for the 1C:Enterprise 8.1 platform, without reference to any specific configuration and additional service capabilities implemented in it.

So, open the document journal “Customer Documents”. In the standard state, without selection enabled, all documents of all buyers are displayed on the screen (even in the demo database this takes up more than one page).

We need to quickly look at all the documents of the counterparty Simon and Schuster LLC. The task is implemented as follows: a selection window opens, in the “Counterparty” element, select the counterparty “Simon and Schuster LLC” from the directory (Fig. 6) and click “OK”. The problem is solved (Fig. 7).

Rice. 6

Rice. 7

To quickly use the desired selection, it is enough to immediately begin selecting the desired criterion. There is no need to check the box next to the selection used. The program will do this itself after the necessary criterion has been specified. You can quickly apply the configured selection criteria by pressing the key combination Ctrl+Enter*.

Please also note that when specifying values ​​in the selection elements (in our example, the counterparty "Simon and Schuster LLC"), in most cases the quick selection mechanism, widely used in the 1C:Enterprise 8.1 platform, will work. In our example, it was enough to type the first characters of the counterparty’s name or its code directly into the selection value field, press Enter or Tab, and the system would automatically “guess” the directory element we need.

Keep in mind that quick selection significantly speeds up the selection of values ​​you know. Use it whenever possible.

Now we will display a list of items whose names contain the word “Teapot”. To do this, open the “Nomenclature” directory, call up the selection window, in the “Name” element, select the comparison condition “Contains” and indicate the required word (Fig. 8).

Rice. 8

The “Contains” comparison type was not chosen by chance. It is he who allows you to find the right word(or part thereof) anywhere in the nomenclature name. If you leave the comparison type "Equals", the list will display only those items of nomenclature that are named exactly as the query is written. But there are no such items in our demo database (that is, not a single element would be displayed).

Since in our demo database the item has the “Full name” attribute, it is present in the list of available selections as a separate position. But at the same time, only “Contains”/“Does not contain” are available as comparison conditions. The reason is that the “Full name” attribute in our demo database is a string of unlimited length.

If you search not by name, but by full name, it is better to uncheck the box for using selection by name. Otherwise, selection will be used both by name and by full name, which may not always be necessary (Fig. 9).

Rice. 9

For the convenience of working with directory elements when using selections, it is better to temporarily turn off the display of the hierarchy (Fig. 10).

Rice. 10

As a third task, let's look at only the documents "Sales of goods and services" in the document log "Customers' Documents". The solution to this problem will demonstrate the peculiarity of one of the types of selection, available only in document journals.

Open the "Customers' Documents" journal and indicate in the "Document Type" selection element the document "Sales of Goods and Services". The program itself provides the ability to select a specific type of document from those included in this journal. There is also a personal button on the action panel for this (Fig. 11).

Rice. eleven

Similar to selection by type of document, the platform will automatically offer the selection element “Subordination Structure” if the configuration has configured relationships between documents.

Let's try to view in the document log "Buyers' Documents" only the "Invoice" documents for counterparties located in the "Buyers" group of the directory. This task is similar to the first one, with the only difference that we do not specify a specific counterparty, but analyze the invoices of a whole group of counterparties that are in the “Buyers” group.

There are several ways to implement the task. Let's look at the most effective one. Open the "Buyers' Documents" journal and indicate in the "Document Type" selection element the "Invoice issued" document. After that, in the selection element “Counterparty” we indicate the type of comparison “In group”. Select the value type "Counterparties" and open the counterparty selection form. In it, select the “Buyers” group (Fig. 12).

Rice. 12

This type of comparison implies that the condition will be satisfied by all directory elements that are in the specified group. Moreover, even if there are subgroups within the specified group, all elements nested there will also satisfy this criterion.

Now let’s complicate the task: let’s look at only the “Invoice” documents in the “Buyers’ Documents” document log, but at the same time we need to look at documents for both buyers and suppliers. We solve it similarly to the previous one, with the exception of one imposed selection criterion.

In human language, the task is formulated as follows: “display all the necessary documents for all contractors located in the directory groups Buyers, Suppliers, Suppliers for Sale.” This is easy to implement - in the “Counterparty” selection condition, select “In a group from the list” (Fig. 13). After this, a mechanism for filling out the list becomes available, to which you can add the necessary groups (or elements) of the directory. Moreover, the list can be filled out manually. By analogy with solving the previous problem, find the desired group and select it, repeating this for each new element of the list. However, it is more advisable to use a convenient selection mechanism, which is automatically implemented by the program. With the help of selection, it is much easier and faster to fill the list with the necessary components. After the list is completed, click “OK” and activate the configured selection criteria. The problem is solved.

Rice. 13

The difference between the selection condition “In a group from the list” and the condition “In the list” is that in the first case the condition will be satisfied for all elements of the directory that are either explicitly specified in the list or located within the groups specified in the list. The second case simply tells the program to check the list when selecting. That is, if you specify a group there, then the group itself will be included in the selection (as independent element directory), and the elements that are included in it will not satisfy the selection criteria. It was possible to solve the problem “head-on” - select the condition “In the list” and, using selection, add there all the elements contained in the necessary groups.

The selection conditions “Not in the list” and “Not in the group from the list” allow you to specify the required data sets that should not be included in the selection. That is, the problem could be solved in the opposite way - specify “Not in a group from the list” and add all the directory groups there, except for the three necessary ones (Buyers, Suppliers, Suppliers for sale).

And finally, in the document “Sales of goods and services” with number TDN00002, we will display in the tabular section only the nomenclature containing the word “STINOL” in the names. This task will allow us to see the universality of the selection mechanism itself, as well as how diverse its use can be. The task is based on real situations when, for example, there are several hundred (or even thousands) of lines in the tabular part of the invoice and you need to quickly analyze its composition. In our demo database there is a document “Sales of goods and services” with number TDN00002, which has several rows in the tabular section. Despite the fact that there are no buttons for using selections in the document, calling the selection is available through the context menu (right mouse button).

Next, we impose selection criteria in the context of Nomenclature. Another question immediately arises - how to select “STINOL” if the conditions for selecting the nomenclature are quite limited (available “Equal”, “Not equal”, “In the list” and “Not in the list”). The problem can be solved under such conditions. We select the condition “In the list”, open the already familiar window for filling out the list, and use the selection of items (Fig. 14, 1). There may be a large number of elements in the nomenclature list, so we won’t look around for the required items. Let's use the selection in the list of items, specifying the selection condition "Contains" for the name of the item (Fig. 14, 2).

Rice. 14

After this, all that remains is to add the selected items to the selection list for the document and apply the selection criteria.

Useful features of the selection mechanism

Let's consider a few more points related in one way or another to selections in the 1C:Enterprise 8.1 system.

In lists of configuration objects that use date binding (for example, lists of documents), and in document logs, you can use quick filtering by date range. To do this, just click the corresponding “Period Setting” button on the action bar (or select it in the context menu) (Fig. 15). In this way, you can individually adjust the criterion for displaying elements by date in each list.

Rice. 15

You can quickly set selection by value in the current list cell by clicking on the “Selection by value in the current column” button (Fig. 16). When you click on this button, selection will be made based on the current value in the column. This function works only for those columns whose details can be used to set selections. If the list already uses any selection, the new one will be attached to it. Moreover, the button becomes active for this column. You can cancel such a selection in the same way - by “squeezing” the button in the corresponding column.

Rice. 16

Another useful property selection mechanism - maintaining selection history (Fig. 17). The program remembers which selections have been set, so you can quickly return to any of them by simply selecting it from the drop-down list. By the way, in the “Selection and Sorting” window, pay attention to the “Selections” button (Fig. 6). By clicking on this button, you will be taken to an interface where you can save and restore selection settings so that you can return to them later (Fig. 18).

Rice. 17

Rice. 18

And the last function is to cancel all selections (Fig. 19). Clicking the "Disable selection" button disables all selections installed in the list. A similar action can be achieved by opening the “Selection and Sorting” window and unchecking all active elements.

Rice. 19

The program also has the ability to quickly search by document number in lists of documents or in journals. This feature is called up by the “Search by number” button (Fig. 20) and allows you to find required document, flexibly configuring search parameters. Documents found based on the specified criteria are displayed at the bottom of the window of this service, and you can go to the desired document.

/
Implementation of data processing

Ordering query results

1.1. If the algorithm for processing query results depends on the order of records in the query or if the result of query processing in one form or another is presented to the user, then the sentence should be used in the query text SORT BY. In the absence of expression SORT BY no assumptions can be made about the order in which the records will appear in the query results.

Typical examples of problems that may occur:

• different sequence of rows in the tabular section when filling according to query results;
• different order of data output (rows, columns) in reports;
• different filling of document movements based on query results (*).

The likelihood of different results occurring when performing the same actions increases

• when transferring information base to another DBMS
• when changing the DBMS version
• when changing DBMS parameters

* Note: ordering the results of queries that generate movements is justified only if the ordering is part of the algorithm for generating movements (for example, writing off the balances of batches of goods using FIFO). In other cases, records should not be sorted, since additional ordering will create an excessive load on the DBMS.

1.2. If the results of a query must be displayed to the user in some way, then

• it is necessary to organize the results of such queries by fields of primitive types;
• Ordering by fields of reference types should be replaced by ordering by string representations of these fields.

Otherwise, the order of the lines will appear random (inexplicable) to the user.

See also: Sorting value table rows

1.3. No offer SORT BY justified only in cases where

• the algorithm for processing query results does not rely on a specific order of records
• the result of processing the executed request is not shown to the user
• the result of the query is obviously one record

Shared use with VARIOUS design

2. If the request uses the construction VARIOUS, ordering should be performed only by fields included in the selection (in the section CHOOSE).

This requirement is associated with the following feature of query execution: ordering fields are implicitly included in the selection fields, which in turn can lead to the appearance of several rows with the same values ​​of the selection fields as a result of the query.

Restrictions on the use of the AUTO ORDER construction

3. Use of the design FIRST together with the structure AUTO ORDER forbidden.

In other cases, the design AUTO ORDER It is also not recommended to use, since the developer does not control which fields will be used for ordering. The use of such a design is justified only in cases where the resulting order of records is not important, but it must be the same regardless of the DBMS used.

Requests are designed to extract and process information from the database to provide it to the user in the required form. Processing here means grouping fields, sorting rows, calculating totals, etc. You cannot change data using queries in 1C!

The request is executed as per the given instructions − request text. The request text is compiled in accordance with the syntax and rules query language. The 1C:Enterprise 8 query language is based on the standard SQL, but has some differences and extensions.

Scheme of working with a request

The general scheme of working with a request consists of several successive stages:

1. Creating a Request object and setting the request text;
2. Setting request parameters;
3. Executing a request and getting the result;
4. Bypassing the request result and processing the received data.

1. Object Request has the property Text, to which you need to assign the request text.

// Option 1
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM

|WHERE
;

// Option 2
Request = New Request("CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency");

2. Setting parameter values ​​is carried out using the method SetParameter(< Имя>, < Значение>) . Parameters in the request text are indicated by the symbol “ & " and are usually used in selection conditions (WHERE section) and in virtual table parameters.

Request);

3. After assigning the text and setting the parameters, the request must be executed and the execution result obtained. Execution is carried out by the Execute() method, which returns an object Query Result. From the query result you can:

• get a selection using the Select method (< ТипОбхода>, < Группировки>, < ГруппировкиДляЗначенийГруппировок>) ;
• upload values ​​to a value table or value tree using the Upload method (< ТипОбхода>) .

// Receive a sample

Sample = Query Result. Choose();

// Getting a table of values
RequestResult = Request. Run();
Table = Query Result. Unload();

4. You can bypass the query result selection using a loop:

Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

A complete example of working with a request might look like this:

// Stage 1. Creating a request and setting the request text
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency";

// Stage 2. Setting parameters
Request . SetParameter("Currency" , SelectedCurrency);

// Stage 3. Executing the query and getting the sample
RequestResult = Request. Run();
Sample = Query Result. Choose();

// Traversing the selection
Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

Composition of the request text

The request text consists of several sections:

1. Request Description— list of selectable fields and data sources;
2. Merging queries— expressions “UNITE” and “UNITE ALL”;
3. Organizing results— the expression “ORDER BY...”;
4. Auto-order— the expression “AUTO ORDERING”;
5. Description of results- the expression “RESULTS ... BY …”.

Only the first section is mandatory.

Temporary tables and batch queries

1C query language supports the use temporary tables— tables obtained as a result of executing a query and stored on a temporary basis.

You can often encounter a situation where you need to use not database tables as the source of a query, but the result of executing another query. This problem can be solved using nested queries or temporary tables. The use of temporary tables allows you to simplify the text of a complex query by dividing it into its component parts, and also, in some cases, speed up query execution and reduce the number of locks. To work with temporary tables, use the object TimeTable Manager. A temporary table is created using the PLACE keyword followed by the name of the temporary table.

ManagerVT = New TemporaryTablesManager;
Request = New Request;
Request . ManagerTemporaryTables = ManagerVT;

Request . Text =
"CHOOSE
| Currencies.Code,
| Currencies.Name
|Place in Currency
|FROM
| Directory.Currencies AS Currencies";

RequestResult = Request. Execute();

To use the VTVcurrency temporary table in other queries, you need to assign a common temporary table manager to these queries—VT Manager.

Batch request is a request that contains several requests separated by the “;” character. When executing a batch query, all queries included in it are executed sequentially, and the results of all temporary tables are available to all subsequent queries. Explicitly assigning a temporary table manager to batch queries is not necessary. If a temporary table manager is not assigned, then all temporary tables will be deleted immediately after the query is executed.

For batch queries, the ExecuteBatch() method is available, which executes all queries and returns an array of results. Temporary tables in a batch query will be represented by a table with one row and one column “Count”, which stores the number of records. To debug batch requests, you can use the method Execute Batch WITH INTERMEDIATE DATA() : It returns the actual contents of temporary tables, not the number of records.

// Example of working with a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Currencies.Name
|FROM
| Directory.Currencies AS Currencies
|;
|SELECT
| Nomenclature.Name
|FROM
| Directory. Nomenclature AS Nomenclature";

Batch Result = Request. ExecuteBatch();

TZCurrencies =PacketResult[ 0 ]. Unload();
TZNomenclature = Package Result[ 1 ]. Unload();

// An example of using temporary tables in a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Products. Link HOW TO Product
|PLACE VTProducts
|FROM
| Directory.Nomenclature HOW Products
|WHERE
| Products.Manufacturer = &Manufacturer
|;
|SELECT
| VTTProducts.Product,
| Vocational school. Quantity,
| Vocational school.Price,
| Vocational school.Link AS DocumentReceipts
|FROM
| VT Products AS VT Products
| LEFT CONNECTION Document. Receipt of Goods and Services. Goods AS PTU
| Software VTProducts.Product = PTU.Nomenclature";

Request . SetParameter( "Manufacturer", Manufacturer);

RequestResult = Request. Run();
Sample = Query Result. Choose();

Bye Sample.Next() Loop

EndCycle;

Virtual tables

Virtual tables- these are tables that are not stored in the database, but are generated by the platform. At their core, these are nested queries against one or more physical tables executed by the platform. Virtual tables receive information only from registers and are mainly intended for solving highly specialized problems.

The following virtual tables exist (possible parameters are indicated in parentheses):

• For information registers:
  • SliceFirst(<Период>, <Условие>) — the earliest records for the specified date;
  • SliceLast(<Период>, <Условие>) — the latest records for the specified date;
• For accumulation registers:
  • Leftovers(<Период>, <Условие>) — balances as of the specified date;
  • Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <Условие>) - Period transactions;
  • RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <Условие>) — balances and turnover for the period;
• For accounting registers:
  • Leftovers(<Период>, <УсловиеСчета>, <Субконто>, <Условие>) — balances as of the specified date by account, dimensions and sub-accounts;
  • Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчета>, <Субконто>, <Условие>, <УсловиеКорСчета>, <КорСубконто>) — turnover for the period in the context of accounts, measurements, corr. accounts, subconto, cor. subconto;
  • RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <УсловиеСчета>, <Субконто>, <Условие>) — balances and turnover in the context of accounts, measurements and sub-accounts;
  • TurnoverDtKt(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчетаДт>, <СубконтоДт>, <УсловиеСчетаКт>, <СубконтоКт>, <Условие>) — turnover for the period by account Dt, account Kt, Subconto Dt, Subconto Kt;
  • MovementsSubconto(<НачалоПериода>, <КонецПериода>, <Условие>, <Порядок>, <Первые>) — movements together with subconto values;
• For calculation registers:
  • Base(<ИзмеренияОсновногоРегистра>, <ИзмеренияБазовогоРегистра>, <Разрезы>, <Условие>) — basic data of the calculation register;
  • DataGraphics(<Условие>)—graph data;
  • ActualActionPeriod(<Условие>) is the actual period of validity.

When working with virtual tables, you should apply selections in the parameters of virtual tables, and not in the WHERE condition. The query execution time greatly depends on this.

Query constructor

To speed up the input of query texts, the platform has special tools: Query constructor And Query constructor with result processing. To call constructors, you need to right-click and select the required item:

Constructors can also be called from the main menu Text.

Using the query builder, the programmer can interactively construct the query text. To do this, select the necessary tables and fields with the mouse, establish relationships, groupings, totals, etc. This approach saves time and eliminates possible errors. As a result of its work, the query constructor generates the query text.

The query constructor with result processing, in addition to generating the query text, creates a ready-made code fragment for receiving and processing data.

RequestSchema object

The platform allows you to programmatically create and edit the request text using the object Request Schema. An object has a single property Batch of Requests, in which the object stores the properties of all queries currently being edited. The RequestSchema object supports the following methods:

• SetQueryText(< Текст>) — fills the Query Packet property based on the submitted request text;
• GetQueryText() - returns the request text generated based on the Request Packet property;

FindParameters() - returns the request parameters.

Let's look at an example of working with the RequestSchema object. To programmatically generate the request text

SORT BY
Currencies.Code

The embedded language code might look like this:

RequestScheme = New RequestScheme;
Package 1 = RequestScheme. RequestBatch[ 0 ];
Operator1 = Package1. Operators[ 0 ];
// adding source
RegisterTable = Operator1. Sources. Add( "Directory.Currencies", "Currencies" );
// adding fields
FieldLink = Operator1. SelectableFields. Add("Currencies.Link" , 0 );
FieldCode = Operator1. SelectableFields. Add("Currencies.Code", 1);
// specifying field aliases
Package 1 . Columns[ 0 ]. Alias ​​= "Currency" ;
Package 1 . Columns[ 1 ]. Alias ​​= "Code" ;
// adding a condition
Operator1 . Selection. Add( "NOT FlagDeletion");
// add ordering
Package 1 . Order. Add(FieldCode);
RequestText = RequestScheme. GetQueryText();

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; it is used to change data object model data.

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

Example request:

CHOOSE
Banks.Name,
Banks.CorrAccount
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. You can't 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 Goods. QuantityRemaining IS NULL

The data type 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 the full one 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 it from many tables, transforming them into the required form.

CHOOSE
Products in Warehouses Remains and Turnover. Nomenclature,
ProductsInWarehousesRemainingAndTurnover.QuantityInitialRemaining,
ProductsInWarehousesRemainsAndTurnover.QuantityTurnover,
GoodsInWarehousesRemainsAndTurnover.QuantityIncoming,
GoodsInWarehousesRemainsAndTurnover.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 steps 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 request will be: data retrieval, creation of a temporary table, or destruction of 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 query console header, 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:

Also in this menu there are 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 not optimal performance queries 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(ProductsOrganization.Registrar AS Document.Sales 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!