2

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'

4
  • 1
    MySQL ABS cannot work on strings like "5-6". It operates only on numbers. What you want will always be impossible on the data you have, by design. Please edit your question to provide requirements that are technically possible :)
    – Mołot
    Oct 11, 2013 at 14:04
  • I tryed on my db and ABS works fine ^^
    – Shyghar
    Oct 11, 2013 at 14:27
  • It is not bound to fail, it's just unsafe and it may return unexpected result or make database to refuse to process your query.
    – Mołot
    Oct 11, 2013 at 14:31
  • What Drupal version? Please tag your question with the right Drupal version.
    – leymannx
    Jan 13, 2017 at 15:58

2 Answers 2

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

1
  • nice solution! works perfectly Mar 13, 2018 at 10:07
0

for the Drupal8 we can do like:

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.