Specifics of sorting by reference fields





When you work with queries, it is sometimes necessary to sort query results by presentation of some reference field. For example, you might need to sort a product list by presentation. Note that sorting directly by the Presentation field leads to a somewhat unexpected result: data is sorted according to the ascending order of object references. The reason of this effect is the fact that the Presentation field is a virtual one and at the database level it consists of multiple fields. The presentation value is formed based on these fields at the moment it is retrieved. If sorting by this field is performed, sorting by the actual fields that form it is applied consequently, and the result is sorted by internal field reference instead of by string presentation.

Example of incorrect sorting:

SELECT
    Items.Code,
    Items.Description,
    Items.PurchasePrice
FROM
    Catalog.Items AS Items
WHERE
    Items.IsFolder = FALSE
ORDER BY 
    Items.Presentation

In reality, sorting by presentation is questionable in terms of the usefulness of the result. For example, let us take a look at document presentations: these strings contain dates, and sorting dates as strings does not provide any meaningful result. To ensure that sorting meets user expectations, use sorting directly by reference field and add the AUTOORDER keyword to the query. If this is the case, a query retrieves actual fields for each reference field required to sort it (date and number for documents, default presentation for catalogs) and applies sorting by these actual fields.

Example of correct sorting:

SELECT
    Items.Code,
    Items.Description,
    Items.PurchasePrice
FROM
    Catalog.Items AS Items
WHERE 
    Items.IsFolder = FALSE
ORDER BY
    Items.Ref
AUTOORDER

Comments
0
Add comment