Custom sort in Drupal Views

Question

I have a View that uses fields from the Biblio module and those are unfortunately all defined as text fields. I need to sort my view according to the accession number, but if define the sorting in the View it sorts the numbers wrong like

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20 ...

instead of

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ...

I assume this is because Views interprets those as string and sorts them accordingly.

Is there an easy way to create a custom sort function that would sort those as numbers? Or maybe I could override the autogenerated SQL (I know how to do that), but I don't know how to sort strings as numbers in SQL.

Answer 1

You can use CAST or CONVERT to make the text into numbers and just do normal sorting, documentation

Answer 2

Consider using the computed field module ( http://drupal.org/project/computed_field ). It allows you to set the value of the field as a computed value of other CCK fields in the content type.

Make a computed field. Set the data type to be an Integer. In the settings for computed field let

$node_field[0]['value'] = (int)($node->field_biblio_that_has_text[0]['value']);

Now you can sort on the basis of the computed field and sorting will happen numerically and not textually. Of course you need to change field_biblio_that_has_text to the machine name of the biblio field (go to manage fields and you will find the name there).

To learn more about computed field (and learn when exactly computed field is calculated) check out http://drupal.org/node/126522