Drupal 7 Views Sorting Using a MySQL Function

Views is a very powerful query builder, but it can be challenging to make it solve specific problems that it wasn't intended to address.

For instance, a client recently wanted to sort forum topics and other kinds of content according to how interested the users are in the topic of each piece of content. In this case, "interest" was indicated by users filling out two fields in their user profile: their primary specialization and any additional interests they have. Content posted to the site is categorized using the same taxo vocabulary used in those user profile fields. The goal was to add a custom sort that would rank results in the order of these matches:

• specialization and interests
• just specialization
• just interests
• no match

Moreover, this sorting algorithm had to be applied either first and foremost (in the case of news items etc that were to be brought to their attention on a dashboard), or secondary to existing views sorting by date, in cases where the most relevant items should appear atop less relevant items posted in the same period.

My solution was to code this as a custom MySQL function, and to apply that function using hook_views_query_alter(). This does suffer the weakness of being a MySQL-specfic solution, but given the hosting situation, it was safe choice in this case. This also means you need to use literal MySQL table and field names in the query -- again, not appropriate for a generic solution, but fine for an existing table over which you have control.

Here's the custom function I created. (Note that I've changed function names, etc. for the purposes of nondisclosure.) It lives in its own file called ExampleCustomSort.sql:

CREATE FUNCTION ExampleCustomSort (
  param_uid INT,
  param_nid INT
)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE specialty_count, interests_count, sort_value INT;
    
    SET specialty_count = (SELECT COUNT(*) from taxonomy_index WHERE nid = param_nid AND tid IN (SELECT field_specialization_tid FROM field_data_field_specialization WHERE entity_id = param_uid));
    SET interests_count = (SELECT COUNT(*) from taxonomy_index WHERE nid = param_nid AND tid IN (SELECT field_interests_tid FROM field_data_field_interests WHERE entity_id = param_uid));
    
    IF specialty_count > 0 THEN
      IF interests_count > 0 THEN SET sort_value = 3;
      ELSE SET sort_value = 2;
      END IF;
    ELSEIF interests_count > 0 THEN SET sort_value = 1;
    ELSE SET sort_value = 0;
    END IF;
    
    RETURN sort_value;
END

I enabled this function in the module's install file:

/**
 * Implements hook_enable().
 */
function example_module_enable() {
  $drop = db_query('DROP FUNCTION IF EXISTS ExampleCustomSort;');
  $path = drupal_get_path('module', 'example_module');
  $sql  = file_get_contents("$path/ExampleCustomSort.sql");
  $result = db_query($sql);
}

/**
 * Implements hook_uninstall().
 */
function example_module_uninstall() {
  $drop = db_query('DROP FUNCTION IF EXISTS ExampleCustomSort;');
}

And then insert it into the views query building process using hook__views_query_alter(). Note that there are three views being altered. In one we wanted to sort primarily by the new custom sort, and in the other two it is applied in addition to the existing views sorting:

function example_module_views_query_alter(&$view, &$query) {
  global $user;
  
  $views_sorted_first = array('dashboard_content');
  $views_sorted_last  = array('videos', 'articles');
  
  $sort_first = in_array($view->name, $views_sorted_first);
  $sort_last  = in_array($view->name, $views_sorted_last);

  // Only operate if user is authenticated and view needs relevance sorting.
  if ($user->uid && ($sort_first || $sort_last)) {
    $relevance_sort = array(
      'field' => '(SELECT ExampleCustomSort(' . $user->uid . ', node.nid))',
      'direction' => 'DESC',
    );

    // Prepend if sorting first.
    if ($sort_first) {
      array_unshift(
        $view->query->orderby,
        $relevance_sort
      );
    }

    // Append if sorting last.
    else {
      $view->query->orderby[] = $relevance_sort;
    }
  }
}

And voila! The MySQL function runs on the database layer, and returns a simple 3, 2, 1, or 0 value that is used to sort the results. You can see how the uid argument is set up in PHP to be used as param_uid in the MySQL function, which will pull node.nid from the views-generated query itself.

Trying to accomplish this using the Drupal database API in the middle of views building would have been considerably messier and less efficient, and this proved very quick to develop and test.