Order a text field numerically in views

Question

I need to reorder my views results before display it.

This is my query

SELECT 
commerce_product_field_data_field_magazine__field_data_field_numero.field_numero_value AS commerce_product_field_data_field_magazine__field_data_field, node.nid AS nid
FROM 
{node} node
LEFT JOIN {field_data_field_magazine} field_data_field_magazine ON node.nid = field_data_field_magazine.entity_id AND (field_data_field_magazine.entity_type = 'node' AND field_data_field_magazine.deleted = '0')
INNER JOIN {commerce_product} commerce_product_field_data_field_magazine ON field_data_field_magazine.field_magazine_product_id = commerce_product_field_data_field_magazine.product_id
INNER JOIN {field_data_field_tipo} field_data_field_tipo ON node.nid = field_data_field_tipo.entity_id AND (field_data_field_tipo.entity_type = 'node' AND field_data_field_tipo.deleted = '0')
LEFT JOIN {field_data_field_numero} commerce_product_field_data_field_magazine__field_data_field_numero ON commerce_product_field_data_field_magazine.product_id = commerce_product_field_data_field_magazine__field_data_field_numero.entity_id AND (commerce_product_field_data_field_magazine__field_data_field_numero.entity_type = 'commerce_product' AND commerce_product_field_data_field_magazine__field_data_field_numero.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('magazine')) AND (field_data_field_tipo.field_tipo_value IN  ('rivista', 'dvd')) ))
ORDER BY commerce_product_field_data_field_magazine__field_data_field ASC

and it works fine but I need the same view with a different ordering

ORDER BY ABS(commerce_product_field_data_field_magazine__field_data_field)

because the result's now order like

1 10 11 2 3 4 .. 

but I need

1 2 3 4 10 11 .. 

How can I achieve this changing of ordering?

ps_ I can't change the text field type to int because I have some value like '5-6'

Answer 1

Can you use views instead? The Views Natural Sort module may help you

Otherwise, in drupal 7 you can go with

$query->orderby( 'CAST(n.title AS SIGNED)', 'ASC');

Check also CAST Mysql documentation

Answer 2

for the Drupal8 we can do like:

$query->addExpression('CAST(n.title AS SIGNED)', 'title_as_int');
$query->orderby( 'title_as_int', 'ASC');