Order view: Sort by order number does not sort numerically

The Commerce Order Number field is a string field. So, it will work similarly to a string even though it was used as an integer number. So, it works as designed.

Drupal\commerce_order\Entity\Order.php

$fields = parent::baseFieldDefinitions($entity_type);

    $fields['order_number'] = BaseFieldDefinition::create('string')
      ->setLabel(t('Order number'))
      ->setDescription(t('The order number displayed to the customer.'))
      ->setRequired(TRUE)
      ->setDefaultValue('')
      ->setSetting('max_length', 255)
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);

    $fields['version'] = BaseFieldDefinition::create('integer')

To achieve the sorting as Integer we can able to use Query alter to type cast string as Integer. Below is an example.

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter().
 */
function example_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'VIEW_ID') {
    // Alter Commerce order to sort based on Order number.
    $formula_form = "CAST(order_number AS SIGNED)";
    $query->addField(NULL, $formula_form, 'order_number_field');
    $query->addOrderBy(NULL, NULL, 'DESC', 'order_number_field');
  }
}

This is a feature request not a bug report. I'm aware of why it doesn't sort correctly. I want to implement smart sorting as a feature at some future point. If this is not a feature the commerce maintainers want in commerce core then they can say so and close this as Won't Fix

Read this a bit too quickly when you pinged me, we cannot merge this. This can probably go into your custom code, but we cannot commit this as on several projects I was personally working on, the order number isn't numeric. There is often a prefix or a suffix...

Committing this will break the sorting for people using non strictly numerical order numbers.

ivnish → closed merge request !451

Ok, thanks!

MR with patch will be attached if needed

What I was thinking of was implementing natural sorting like what some filesystem browsers implement. It sorts alphabetically but when it encounters numbers it understands what 10 is and sorts as expected. It does not "cast" anything to a number.

So orders with prefixes would still be alphabetically sorted but any numbers within them would be sorted numerically.

I haven't the slightest idea on how this kind of sorting is achieved but I know it's possible because I've seen it done before.