1

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.

  • So you're altering data postQuery but want them to be sorted by that altered field in query? I think I'm a bit confused about what you're trying to achieve. – zaporylie Jun 18 at 12:41
  • @zaporylie I found no way to boost the relevance during the query, but changing the score post-query and before display (which is set to order by relevance) should be working too imo. – Neograph734 Jun 18 at 20:22
  • Well, the sorting is done in query rather than display, otherwise it would be very-very expensive, so what you're trying to achieve is not gonna work. Imagine 15000000 results you must go through, alter result and sort by value - the solution you're aiming for is simply not scalable. – zaporylie Jun 19 at 7:15
  • @zaporylie I am very aware of that, and I totally agree with you. However I only have about 200 items and since I have no idea how to do it in a query, this appears to be the second best option. – Neograph734 Jun 19 at 15:17
  • For example, you are indexing nodes and want a "flag" or a relevance score. Add a field to the node content type for relevance score, and make sure that field is indexed in Solr. – mbomb007 Jun 19 at 21:40
2

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');
}
1
+150

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.

  • Thanks for your effort Frank, I'll have a look at it this weekend. – Neograph734 Jun 20 at 17:47
  • After a lot more reading I found a way to do in the query, as everybody suggested. But since i cannot award the bounty to myself and you gave a very detailed answer, you got it. Thanks again :) – Neograph734 Jun 26 at 17:30
  • 1
    Thanks @Neograph734. In hindsight I realise I answered a slightly different question than the one you asked so while I appreciate the bounty I feel a little guilty about accepting it. I'll leave my answer here in case it helps someone else, upvoted yours so it will appear first. – Frank H. Jun 26 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.