Using LIKE in search query

Question

I would like to search for words that contains the keyword when a user executes a search. I have tried to hook into hook_query_alter():

function MYMODULE_query_alter(&$query) {
  // Check if this is a search query   
  if (!$query->hasTag('search_node')) {
    return;
  }

  $conditions = &$query->conditions();
  $conditions = &$conditions[1]['field']->conditions();
  // Change the condition to '%<keyword>%'
  // and add LIKE as operator
  $conditions[0]['value'] = "%" . $conditions[0]['value'] . "%";
  $conditions[0]['operator'] = 'LIKE';
}

I can alter the search query, but I'm not getting any results.

The modified query that gets executed looks like this:

SELECT FROM {search_index} i
  INNER JOIN {node} n ON n.nid = i.sid
  INNER JOIN {search_total} t ON i.word = t.word
  WHERE (n.status = :db_condition_placeholder_0)
    AND ((i.word LIKE :db_condition_placeholder_1 ESCAPE '\\'))
    AND (i.type = :db_condition_placeholder_2) 
  GROUP BY i.type, i.sid
  HAVING  (COUNT(*) >= :matches)

And the placeholders in the SelectQuery object are set to:

[:db_condition_placeholder_0] => 1
[:db_condition_placeholder_1] => %liv%
[:db_condition_placeholder_2] => node

I can verify that the tables search_index and search_total both contain a couple of words that contain liv, so this should return some results.

I have also tried Fuzzy Search module, but I don't know how I can connect the provided search to the ordinary search form.

Notice that I have tried db_like(), but without any success.

I don't understand why it should be so hard to change the default search to search through the index using a LIKE rather than = condition?

Something like this should work if I only knew how to implement it:

$keyword = $_GET['s'];
$status = 1;
$types = array('article', 'contact');

db_query("SELECT node.nid FROM {node} n
  INNER JOIN {search_index} si 
    ON si.sid = n.nid 
    AND si.word LIKE :keyword
  INNER JOIN {search_dataset} sd ON sd.sid = n.nid
  WHERE n.status = :status AND n.type IN (:types)
  GROUP BY n.nid", array(
    ':keyword' => '%' . db_like($keyword) . '%',
    ':status' => $status,
    ':types' => $types
  )
);

Answer 1

you connect the the fuzzy search via search api pages module (or views).

when using views you can place the views search block in your desired region.

Manual instructions: (from fuzzy search)

• From the main Search API configuration page, create a server using the Fuzzy Search service class.
• Create an Index using the server you just created.
• Click the Workflow Tab.
• Enable "Complete entity view".
• Enable the following processors: Both Fuzzy search processors, Ignore case, HTML filter, and Tokenizer.
• Move the Fuzzy Search excerpt processor to the bottom of the list, and the Fuzzy Search search settings processor just above it.
• The default settings should be fine.
• You will need the Search API Pages module or Views to provide a place to search your site.
• Go to the main Search API page,
• Click Search Pages, add a search page, and configure with the index you created above, multiple terms for query type, and view mode as themed as search results.
• For Views, make a new view using the default fuzzyseach index, add a page display and configure as desired.
• Run cron to index your content. Visit the path for your search page.

Answer 2

You get no results with your altered query because of what SearchQuery::parseSearchExpression() does to it. Specifically, around line 300:

// Single ANDed term.
else {
  ...
  $this->conditions->condition('d.data', "% $key %", 'LIKE');
  ...
}

Notice the spaces in the condition.

To get any results you'd have to remove those spaces from the query which isn't a simple task. I believe something like this would be required:

function my_module_query_alter($query) {
  if ($query->hasTag('search_node')) {
    $conditions = &$query->conditions();
    $conditions = &$conditions[1]['field']->conditions();
    $conditions[0]['value'] = '%' . $conditions[0]['value'] . '%';
    $conditions[0]['operator'] = 'LIKE';
  }

  if ($query instanceof PagerDefault) {
    PagerDefaultHelper::alter($query);
  }
}

class PagerDefaultHelper extends PagerDefault {
  public static function alter(PagerDefault &$query) {
    $search_query = &$query->query;
    SearchQueryHelper::alter($search_query);
  }
}

class SearchQueryHelper extends SearchQuery {
  public static function alter(SearchQuery &$query) {
    $conditions = &$query->conditions->conditions();
    $conditions[0]['value'] = str_replace(' ', '', $conditions[0]['value']);
  }
}

However, this will only work when searching for a single word. You would have to build on this approach for searches that use multiple words, OR'd words or excluded words.

Answer 3

I think if I add db_like($prefix) using db_like may be it worked!

(i.word LIKE :db_condition_placeholder_1 ESCAPE '\\')

Convert into:

 condition('i.word', db_like($prefix) . '%', 'LIKE')

I am not expert in customization but may be it helpful for you!

Answer 4

I'm not sure that this is the best way to solve this problem, but I ended up using hook_node_update_index() to support searching for parts of words:

function hook_node_update_index($node)
{
   $result = '';

   // how long should each part be?
   $options = array(3, 4, 5);

   // only look at the body and title
   $content = $node->body['und'][0]['value'] . ' ' . $node->title;
   $content = strip_tags($content);
   $content = drupal_strtolower($content);

   $words = explode(' ', $content);

   $parts = array();

   // split words into parts
   foreach ($words as $word) {
     // if the length is less than the size we would like to split at
     // then there's no need to continue
     $length = drupal_strlen($word);

     foreach ($options as $option) {
        if ($length < $option) {
           $result .= ' ' . $word;
           break;
        }
        for ($i=0; $i < ($length - $option + 1); $i++) {
           $parts[] = drupal_substr($word, $i, $option);
        }     
     }
   }

   // append all created parts for the words
   if(count($parts)) {
      $result .= ' ' . implode(' ', $parts);
   }

   return $result;
}

From the documentation:

function hook_node_update_index

Act on a node being indexed for searching.

This hook is invoked during search indexing, after node_load(), and after the result of node_view() is added as $node->rendered to the node object.

I add this as an answer, since it might help someone in the future. I still looking for a better solution that uses a LIKE conditions for each keyword.