Drupal - Views custom sorting

In the end I created a custom module with a Views Sort handler to write the custom ORDER sql.

function custom_module_views_query_alter(&$view, &$query) {
  if($view->name === 'viewname') {
    /**
     * Order = (rating * 10) – (hours old / 2)
     * Note: the rating field is divided by 2 because in the DB the rating is out of 100
     */
    $view->query->orderby[0]['field'] = "(if(field_data_field_rating.field_rating_rating, field_data_field_rating.field_rating_rating,0) / 2) - ROUND((UNIX_TIMESTAMP(NOW())-created)/60/60/2)";
    $view->query->orderby[0]['direction'] = "DESC";

    $view->query->orderby[1]['field'] = "created";
    $view->query->orderby[1]['direction'] = "DESC";
  }
}

For this to work you either have to add the SQL JOINS in this module or add the rating and posted date fields as a 'sort' in your view.

I would use the Views PHP module for this matter.

This module allows the admin account (user 1) to add fields, filters and sorts to views which use PHP code.

• Install and enable the module (along with Views)
• Add all necessary fields in your view and exclude from display
• Add a new Global: PHP field where you specify the code you need in order to calculate the desired result.
• In the value code add: return (($rating * 10) - $age); (also consult the collapsed available variables section).
• In the output code add: <?php print $value; ?>
• Add a new Global: PHP sorting field with: if ($row1->php < $row2->php) return -1; else return 1;