Skip to main content
Gotcha! Altering Search Queries in Drupal 7

Gotcha! Altering Search Queries in Drupal 7

When building a website, regardless of the technologies used, sometimes you want certain pages to be publicly accessible, but not show up in results from in-site searches for one reason or another. The problem with execution of this with a community driven content management system such as Drupal is that you are working with modules written by different developers and every one does things a little differently … even within the guidelines. 

I recently ran into this when a client handed me a Drupal 7 site. The client had an 'Event' content type for all of their conferences and public meetings, but some events they did not want to show up in search results. None of them ever seemed to have the same criteria. Normally, I'd have installed one of the various community search filtering modules, but without rhyme or reason in selecting events to be omitted from search results, none of them would have achieved the end goal.

I remembered seeing a blog post written by the staff at Phase 2 Technology titled Restricting Search Results in Drupal 7 so I dug through their posts and found the article and re-read it and concluded that it had the base of what I needed to give my client what they needed.

Since I wrote the original client code to solve the problem, Phase 2 has updated their site and the original article is no longer available. You can read a cached copy of the blog post on Google's Web Cache.

In short, to solve the problem of the onsey-twosey selection of events that were to be hidden, I simply added a true/false checkbox suppress from public to the event content type, then used most of the code from the Phase 2 post, except I added an EntityFieldQuery to search for node ID's that had the suppress from public checkbox checked and used those results in an additional condition to filter out those nodes from the results. Simple and worked like a charm.

Original solution

/**
 * Implements hook_query_alter().
 *
 * This will allow us to remove nodes from search results that are flagged 
 * to 'suppress from public'.
 *
 * @param QueryAlterableInterface $query
 */
function mymodule_query_alter(QueryAlterableInterface $query) {
  $is_search = FALSE;
  foreach ( $query->getTables() as $table ) {
    if ( $table['table'] == 'search_index' ) {
      $is_search = TRUE;
    }
  }

  if ( $is_search ) {
    $nids = [];
    // Run entity field query to get nodes that are 'suppressed from public'.
    $efq = new EntityFieldQuery();
    $efq->entityCondition('entity_type', 'node')
      ->fieldCondition('field_suppress_from_public', 'value', 1, '=');
    $result = $efq->execute();
    if ( isset($result['node']) ) {
      $nids = array_keys($result['node']);
    }
    $query->condition('n.nid', $nids, 'NOT IN');
  }
}

So I thought ...

Some time later, I got another ticket from the client saying that two other listing pages for different content types with search features were not returning any results regardless of what was being searched for. Wonderful! What happened?

First thing I had to do was examine how the two listing pages were built and I saw that they were simple view pages with an exposed filter which I figured was just used to filter on a single field. After looking at the exposed filter, I saw the views were using the "Search: Term" view filter handler provided by Search API Views which is a submodule of Search API module.

When I previewed one of the search views with a search term, I started getting SQL errors something to the effect of "unknown column 'n.nid' in WHERE clause ...". After a few ideas that fell short of the problem, I remembered the previous search modifications I had made a few months ago so I started debugging the hook_query_alter() I had written before. Why were main searches working and these two freaking out?

Then the all the lights in the house came on! As it turns out, Search API Views uses a different table alias in the main query than the core Search module does; the core Search module aliases the node table as 'n' and Search API Views aliases it as 'node'. To fix this, I had to refactor my original code to dynamically set table name in the additional query condition for node ID's to filter out.

To dynamically set the table name, I had to look at the query tables and if the node table was in there, set a variable with the value of the query alias for the table. I also had to take into account that searches could return other entities such as Users or taxonomy Terms, so I also set a Boolean variable for whether it was a node search or not because I knew the 'suppress from public' field only existed on Node entities. The resulting code that now works in both types of searches is listed below.

End solution

/**
 * Implements hook_query_alter().
 *
 * This will allow us to remove nodes from search results that are flagged 
 * to 'suppress from public'.
 *
 * @param QueryAlterableInterface $query
 */
function my_site_query_alter(QueryAlterableInterface $query) {
 $is_search = $is_node_search = FALSE;
 $node_alias = FALSE;
 foreach ( $query->getTables() as $table ) {
   if ( $table['table'] == 'search_index' ) {
     $is_search = TRUE;
   }
   if ( $table['table'] == 'node') {
     $node_alias = $table['alias'];
     $is_node_search = TRUE;
   }
 }

 if ( $is_search && $is_node_search ) {
   $nids = [];
   // Run entity field query to get nodes that are 'suppressed from public'.
   $efq = new EntityFieldQuery();
   $efq->entityCondition('entity_type', 'node')
     ->fieldCondition('field_suppress_from_public', 'value', 1, '=');
   $result = $efq->execute();
   if ( isset($result['node']) ) {
     $nids = array_keys($result['node']);
   }
   if ( count($nids) > 0 ) {
     $query->condition(sprintf('%s.nid', $node_alias), $nids, 'NOT IN');
   }
 }
}

As you can see, the modifications that were made to the original Phase 2 code are minimal, but can wreak havoc on your site because one convention doesn't match the other. Both conventions are used throughout the Drupal 7 code base by the core and contrib developers … myself included! Things like this are what makes developing Drupal frustrate both seasoned and new developers alike, but when you figure it out and get it working, you don't feel so bad.

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.