Working with Queries in the System Language

General
Query Result Iteration Methods
Linear Result Iteration
Hierarchical Result Iteration
Result Iteration by Groups
Working with Selection
Methods for Defining of the Current Record Type

We will use several examples to illustrate work with queries in 1C:Enterprise script. Here is a typical example of using a query:

Query = New Query("SELECT Article.Description Description,             |Article.Parent.Description ParentDescription             |FROM Catalog.Articles Article"); // Execute the query and write result to the QueryResult variable. QueryResult = Query.Execute(); // Get the query result selection. Selection = QueryResult.Select(); // While the selection contains records ... While Selection.Next() Do     // ... display result fields in the message window.     Article = Selection.Description;     Parent = Selection.ParentDescription;     Message("Article: " + Article + " Parent: " + Parent); EndDo;

As we see from this example, work with queries is based on three main objects:

• Query - an object that performs the query. In the example, this object is represented by the Query variable.
• QueryResult - an object that contains the query result. In the example, this object is represented by the QueryResult variable.
• QueryResultSelection is an object that can be used to iterate (i.e. look over) records from the result. In the example, this object is represented by the Selection variable.

Let’s study the QueryResultSelection object in more detail. We will need to use the following query text for this purpose:

SELECT
   Article, Count
FROM
   Document.ExpInvoice.Contents
ORDER BY Article
TOTALS Sum(Count) BY Article Hierarchy

See the query results table :

N	Product	Quantity
1	Sanitaryware	104
2	Faucet	84
3	Faucet	10
4	Faucet	8
5	Faucet	44
6	Faucet	22
7	Mixer	20
8	Mixer	5
9	Mixer	1
10	Mixer	14
11	Furniture	134
12	Table	26
13	Table	1
14	Table	15
15	Table	10
16	Chair	108
17	Chair	55
18	Chair	5
19	Chair	32
20	Chair	16

In this table we have added column N, which is not present in the query result. We will use it later to identify query result records. Summary records in the table are shown in italic, and total values for categories in the catalog are shown in bold.

Query Result Iteration Methods

Linear Result Iteration

The linear method of result iteration is the simplest. In the linear method, the selection yields records in the same sequence as in the query results. In our example, this will be records 1, 2, 3, 4, 5, and so on, up to 20.

To retrieve a linear selection from the result, call the Select method of the QueryResult object without any parameters or with the QueryResultIteration.Linear parameter.

Example:

SelectionMethod = QueryResultIteration.Linear; Selection1 = QueryResult.Select(SelectionMethod); // which is equivalent to the record Selection1 = QueryResult.Select();

Hierarchical Result Iteration

The next method for result iteration is called hierarchical. This iteration is used only for records on a single level. To obtain a hierarchical selection from the result, call the Select() method for the QueryResult object with the QueryResultIteration.ByGroupsWithHierarchy.

Example:

SelectionMethod = QueryResultIteration.ByGroupsWithHierarchy; Selection2 = QueryResult.Select(SelectionMethod);

In our example, selection from the results using hierarchical iteration will be completed only for records 1 and 11, as these are the only two records that are located at the highest level. To illustrate this, let’s assume that our result is a tree, where summary records are nodes, and detailed records are leaves. Here is what we will get:

From this picture, we can see that only records 1 and 11 are located at the first level of the tree and that only these records will be included in the first pass of hierarchical iteration.

The next question would be how to obtain other records of the query result. To do this, you can obtain another selection from the QueryResultSelection object. This selection will be used for iteration over subordinate records of the current selection. In our example, when the Selection2 object is to be placed into record number 1, we will ask it for a hierarchical selection. This way, we will get a selection that will return records with numbers 2 and 7. If Selection2 is to be placed in record number 11, the final hierarchical selection will return records with numbers 12 and 16. This is how the hierarchical iteration for query results works. We should note that you can obtain nested selections of any type from the parent selection. If we request a linear Selection2, placed into record 1, it would return record numbers 2 to 10. Let’s show an example of this.

Example:

Procedure RunQuery()     // Generate a query.     Query = New Query;     // Set the query text     Query.Text = "SELECT              |Article, Count               |FROM              |Document.ExpInvoice.Contents              |ORDER BY Article              |TOTALS Sum(Count) BY Article, Article Hierarchy";     // Execute the query and write the result to the variable     // QueryResult.     QueryResult = Query.Execute();     // Get the selection from the query result.     SelectionMethod = QueryResultIteration.ByGroupsWithHierarchy;     Selection = QueryResult.Select(SelectionMethod);     OutputRecursively(Selection); EndProcedure Procedure OutputRecursively(Selection)     // While the selection contains records...     While Selection.Next() Do         // ... display result fields in the message window         Article = Selection.Description;         Count = Selection.Count;         Message("Article: " + Article + " Count: " + Count);         // Continue subordinate records selection         SelectionMethod = QueryResultIteration.ByGroupsWithHierarchy;         If Selection.RecordType() = QueryRecordType.TotalByHierarchy Then             SubSelection = Selection.Select(SelectionMethod, Selection.Group());         Else             SubSelection = Selection.Select(SelectionMethod);                EndIf;         OutputRecursively(SubSelection);    EndDo; EndProcedure

Result Iteration by Groups

The third and the final method of result iteration is by groups. It is similar to hierarchical iteration, but there is a difference: records with hierarchical totals are treated as detailed records during iteration, rather than as node records. To obtain a result for a group selection query you have to call the Select method for the QueryResult object with the QueryResultIteration.ByGroups parameter.

Example:

SelectionMethod = QueryResultIteration.ByGroupsWithHierarchy; Selection2 = QueryResult.Select(SelectionMethod);

After cycling through all the records, we get the following result: 1, 2, 7, 11, 12, 16.

Example:

Procedure RunQuery()     // Generate a query.     Query = New Query;     // Set the query text     Query.Text = "SELECT              |Article, Count               |FROM              |Document.ExpInvoice.Contents              |ORDER BY Article              |TOTALS Sum(Count) BY Article, Article Hierarchy";     // Execute the query and write the result to the variable     // QueryResult.     QueryResult = Query.Execute();     // Get the query result selection     SelectionMethod = QueryResultIteration.ByGroups;     Selection = QueryResult.Select(SelectionMethod);     // While the selection contains records...     While Selection.Next() Do         // ... display result fields in the message window         Article = Selection.Description;         Count = Selection.Count;         Message("Article: "+Article+" Total by article: "+Count);         OutputChildRecords(Selection.Select());     EndDo; EndProcedure Procedure OutputChildRecords (Selection)     // While the selection contains records...     While Selection.Next() Do         // ... display result fields in the message window         Article = Selection.Description;         Count = Selection.Count;         Message("Article: "+Article+" Count: "+Count);      EndDo; EndProcedure

Working with Selection

The QueryResultSelection object is used to iterate query result records. Selection can be represented as an object that contains a reference to the current result record and gives the program access to all fields of the current record. Three methods are used for query record navigation:

• Next() - go to the next result record according to the selection iteration order. At the first call, the selection will be positioned to the first record. When all records are to be selected, this method will indicate this by returning a False value.
• NextByFieldValue() - get the next record with a value in the given field that is different from the value in the same field for the current record.
• FindNext() - find a record with the specified values in certain fields.

Using the NextByFieldValue Method

You can use this method to group results by field values.

Query example:

SELECT
   Doc.Article, Doc.Recipient, Doc.Count
FROM
   Document.ExpInvoice.Contents Doc
ORDER BY Doc.Article.Description, Doc.Recipient.Description

Result:

N	Product	Recipient	Quantity
1	Mixer	“Giant” Giant	14
2	Mixer	“Giant” "Master"	1
3	Mixer	Mosgortorg	5
4	Faucet	“Giant” Giant	44
5	Faucet	“Giant” "Master"	8
6	Faucet	Mosgortorg	10
7	Faucet	Mosgortorg	22
8	Table	“Giant” Giant	10
9	Table	“Giant” "Furniture"	15
10	Table	Mosgortorg	1
11	Chair	“Giant” Giant	32
12	Chair	“Giant” "Furniture"	55
13	Chair	“Giant” "Master"	5
14	Chair	Mosgortorg	16

We get a linear selection from the query result and we will use the NextByFieldValue method for iteration.

Example:

Selection = QueryResult.Select(); While Selection.NextByFieldValue("Article") Do     // you obtain records No 1, 4, 8, 11     While Selection.NextByFieldValue("Recipient") Do         // first you obtain records No 1, 2, 3         // then records No 4, 5, 6         // then records No 8, 9, 10         // then records No 11, 12, 13, 14     EndDo; EndDo;

Note that we do not select record #7 in the internal cycle, since it has the same Recipient field value as in the previous record.

Note that if we use the Next() method to get records by field value, all records with the same field value specified during the previous call of the NextByFieldValue() method will be selected.

Example:

Selection = QueryResult.Select(); While Selection.NextByFieldValue("Article") Do     // you obtain records No 1, 4, 8, 11     While Selection.Next() Do         // first you obtain records No 1, 2, 3         // then records No 4, 5, 6, 7         // then records No 8, 9, 10         // then records No 11, 12, 13, 14     EndDo; EndDo;

Methods for Defining of the Current Record Type

When a selection is placed into a record, we can use this selection to determine the characteristics of the record. We can use the following methods to get record characteristics:

• Level() - determines the record level in the query result.
• RecordType() - determines whether a record belongs to one of the following types:
  • group total;
  • total by hierarchy;
  • detailed record;
  • grand total.
• Grouping() determines the name of the field used to calculate totals.

To illustrate how this method works , let’s see what will they return for a query that was used as an example in the beginning of this:

SELECT
   Article, Count Count
FROM
   Document.ExpInvoice.Contents
ORDER BY Article
TOTALS Sum(Count) BY Article, Article Hierarchy

The result is shown in a table:

N	Product	Quantity	Level	RecordType	Grouping
1	Sanitaryware	104	0	TotalByHierarchy	Product
2	Faucet	84	1	GroupTotal	Product
3	Faucet	10	2	DetailedRecord
4	Faucet	8	2	DetailedRecord
5	Faucet	44	2	DetailedRecord
6	Faucet	22	2	DetailedRecord
7	Mixer	20	1	GroupTotal	Product
8	Mixer	5	2	DetailedRecord
9	Mixer	1	2	DetailedRecord
10	Mixer	14	2	DetailedRecord
11	Furniture	134	0	TotalByHierarchy	Product
12	Table	26	1	GroupTotal	Product
13	Table	1	2	DetailedRecord
14	Table	15	2	DetailedRecord
15	Table	10	2	DetailedRecord
16	Chair	108	1	GroupTotal	Product
17	Chair	55	2	DetailedRecord
18	Chair	5	2	DetailedRecord
19	Chair	32	2	DetailedRecord
20	Chair	16	2	DetailedRecord