Specifics of sorting by reference fields

When you work with queries, it is sometimes necessary to sort query results by the presentation of some reference field. For example, you might need to sort a product list by the 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 for 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 the 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 to sort 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

Next page: Specifics of using "IN HIERARCHY" clause in query language

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.