Natural sorting of generated fields

[ammannbe]

We are using generated fields to order entries by related categories like this:

{myCategory.orderBy('level DESC').one().lft}

When querying entries and sorting by the generated field:

craft.entries.orderBy('customOrderBy ASC, title ASC')

and outputting the customOrderBy field, the result looks like this:

• 107
• 17
• 29
• 33
• 45

As noted in the Craft CMS docs (which even use a similar example), generated fields are stored and queried as text. That’s why the sorting is technically correct but not what we expect. We want a natural numeric sort.

1. Question: How can we achieve natural sorting on a generated field?
   In the docs of Craft CMS and via Google I couldn't find a solution for this.

2. Question: When I update the related category, the generated field don't update. Is that somehow possible?

Edit:
As a workaround a formatted the number with leading zeros: {{ '%010d'|format(myCategory.orderBy('level DESC').one().lft) }}
In that way the sorting works as expected

[brandonkelly]

You can do this using MySQL’s CAST function:

.orderBy('CAST([[customOrderBy]] AS UNSIGNED) ASC, title ASC')

Question: When I update the related category, the generated field don't update. Is that somehow possible?

That’s a known issue (#17938). I’m hoping to have it fixed soon.