Taxonomy terms view sorting results numerically

Question

I have a taxonomy that's comprised entirely of numeric term names, 1 through 1200 (with a few numbers missing in between). I created a new "Taxonomy terms" view to show all terms in this vocabulary. When I look at the view results, I'd like to see the terms sorted numerically, however the view is displaying the terms alphabetically. I understand the reason for this is because term names are strings, but I'm hoping someone has an answer of how to make the query treat the term names as integers.

Here's an example of the view results as they are displayed now:

• 1
• 10
• 100
• 1000
• 1001
• 1005
• 101
• 1014
• 1015
• 1016
• 1019
• 102
• 1020
• 1024
• 103
• 1034
• 104

Normally in MySQL, you could write SELECT name FROM {taxonomy_term_data} ORDER BY CAST(name AS UNSIGNED). Is there a way to get this same type of CAST() happening in views?

Answer 1

Try using Views Natural Sort

This allows you to emulate MYSQL's behavior of CAST() in order to treat the integers as pure values to sort by.

From the documentation:

Natural Sort also supports Numbers.

Normal Sort:

1 apple
10 apples
2 apples

Natural Sort:

1 apple
2 apples
10 apples

Answer 2

I found a solution which requires a little code. The idea is that we make the term weights the same as the term names. Then we can use views to sort against the term weights.

Using hook_taxonomy_term_presave(), we can update the term weight to match the term name for all terms saved from now into the future. It uses intval($term->name) which nicely gets any numbers at the beginning of a term name (or 0 for alphabetical term names).

/**
 * Implements hook_taxonomy_term_presave().
 */
function mymodule_taxonomy_term_presave($term) {
  // For all terms, make the weight equal to the term name. We can then use weight for
  // numerical sorting to fix the bug where numeric term names sort in character code order.
  $vocab_name = 'vocab_machine_name';
  $vocab = taxonomy_vocabulary_machine_name_load($vocab_name);
  if ($term->vid == $vocab->vid) {
    $term->weight = intval($term->name);
  }
}

The above code will only work for terms that are saved from now into the future.

If you want to update all existing terms, try this function. It only took about 1 second to update 1,000 rows on my development machine:

/**
 * Update existing taxonomy term weights.
 */
function resave_numeric_term_weights() {
  // Get vid from machine_name
  $vocab_name = 'vocab_machine_name';
  $vocab = taxonomy_vocabulary_machine_name_load($vocab_name);

  $result = db_select('taxonomy_term_data', 't')
    ->fields('t', array('tid', 'name'))
    ->condition('t.vid', $vocab->vid)
    ->execute();
  $terms = $result->fetchAll();
  foreach ($terms as $term) {
    // Make the weight equal to the intval() of the term name.
    db_update('taxonomy_term_data')
      ->fields(array('weight' => intval($term->name)))
      ->condition('tid', $term->tid)
      ->execute();
  }

  drupal_set_message(t('Updated term weights for @num terms', array('@num' => count($terms))));
}