How can I conditionally alter search API relevance score?

Question

I must be missing some core concept of search API somehow, but I am stuck. The goal is to alter search API results depending on a flag, like Give flagged products in search API a higher search score? however this time without SOLR, but with database backend instead.

I can see all flagged fields properly indexed, so there is a line that associates the content entity id with the user who flagged, and now I want to increase the relevance score of the results, so that items flagged by the current user are always shown first.

I have hijacked the SearchApiDbService class and I did add the following line in postQuery (I also wonder why this hook does not exist, but that is not relevant for now):

drupal_alter('search_api_db_query_results', $results, $query);

This should allow me to alter the search results right before search_api_db hands them over to the search API right?

So now I use the alter hook as follows:

function mymodule_search_api_db_query_results_alter(&$results, SearchApiQueryInterface $query) {
  global $user;
  $uid = $user->uid;

  // Fetch a list of content entities that the user has flagged.
  $drupal_db_q = db_select('search_api_db_MY_FLAG_FIELD_TABLE', 'f')
    ->fields('f')
    ->condition('value', $uid);
  $items = $drupal_db_q->execute()->fetchCol();

  // Iterate over results and multiply score of flagged items.
  foreach ($results['results'] as $key => &$result) {
    if (in_array($key, $items)) {
      $result['score'] *= 10;
    }
  }

dpm($results);

}

In $results I can see that the scores have changed, but the order of the items on my views results page has not, even though I am sorting by relevance.

It has to be something simple, or I am completely misunderstanding how the score value works somehow?

p.s. Since this is conditionally I don't think altering the search api query directly makes sense because I found no way to boost any field scores there. Directions on how to achieve that are welcome too.

Answer 1

Since everyone was telling me to solve this during the query, I decided to have another look at this approach. My main issue was not being able to order by the flagged status, which was eventually solved with an expression.

In the end I managed to get what I wanted with an implementation of hook_search_api_db_query_alter.

function mymodule_search_api_db_query_alter(SelectQueryInterface &$db_query, SearchApiQueryInterface $query) {
  global $user;
  $uid = $user->uid;

  // Left join indexed flag table into search index.
  $db_query->leftJoin('search_api_db_product_index_field_my_flag_field', 'flag', 'flag.item_id = t.item_id');
  // Set a value of 1 for entities flagged by the given uid, 0 otherwise. 
  $alias = $db_query->addExpression('CASE WHEN flag.value = :uid THEN 1 ELSE 0 END', 'flagged', array(':uid' => $uid));
  // Order by the flagged value (1 or 0), highest first.
  $db_query->orderBy($alias, 'DESC');
}

Answer 2

I did a quick test and found the same as you, i.e. updating the relevance score worked, but the view sort did not seem to take the updated relevance into consideration. As @zaporylie pointed out in the comments, sorting is done in query rather than display, but I'm not sure why the sort you specify in the view settings is not being honoured.

A quick workaround is to sort the results array again in your function after you have updated the relevance, like this:

  uasort($results['results'], function($a, $b) {
    return $b['score'] - $a['score'];
  });

Bear in mind that paging is also done as part of the query. Suppose you have 200 items, and there are 5 for a given user, and you want them to appear first in your results. If your pager returns 20 results at a time, there is no guarantee that the 5 results for the user will be in the first 20 results returned. The only way to guarantee the 5 results are available before you sort is to return all the results together, i.e. do not use a pager. If you want paged results, it might be possible to do some paging in a late-called views hook such as hook_views_post_render() but if that doesn't work you'll have to take a different - and much more complicated - approach. Here is a quick overview of the steps:

Write a custom query handler to execute your search against the Search API index. That way, you will be able to modify the results before they are passed to the view.

You'll need a hook_views_data_alter() function to associate queries to the database table with your custom query handler.

You might also need a custom pager plugin (even though you'll be returning all the results, some pager objects might be missing from your view object if you don't specify a pager).

Use a hook_views_plugins() function to specify your plugins, something like this:

function mymodule_views_plugins() {

  $plugins = array(
    'pager' => array(
      'views_plugin_user_first_search_api_pager' => array(
        'title' => t('Custom pager to put user results first'),
        'handler' => 'views_plugin_user_first_search_api_pager',
        'uses options' => TRUE,
        'parent' => 'full',
      ),
    ),
    'query' => array(
      'my_module_search_api_query' => array(
        'title' => t('My custom Search API query'),
        'help' => t('Custom Search API query to put user results first.'),
        'handler' => 'my_module_search_api_query',
        'parent' => 'SearchApiViewsQuery',
       ),
    ),
  );
  return $plugins;
}

The hook_views_data_alter() function should look something like this:

**
 * Implements hook_views_data_alter().
 */
function hook_views_data_alter(&$data) {

    if (isset($data['search_api_index_my_index']['table']['base']['query class'])) {
      $data['search_api_index_my_index']['table']['base']['query class'] = 'my_module_search_api_query';
    }
}

Your search query handler should override the standard SearchAPIViewsQuery handler so it should begin

class my_module_search_api_query extends SearchAPIViewsQuery {

    public function execute(&$view) {

        // Your code here
    }
}

and your pager plugin should override views_plugin_pager_full, like this:

class views_plugin_user_first_search_api_pager extends views_plugin_pager_full {

    function query() {
        // Your code here
    }
}

Hopefully the above will point you in the right direction if you need a pager.