I've added 'search:search terms' as the 'filter' criteria for the a view but if I type in more then one word no results are shown. eg if I type in 'summer' results with summer show but if I type in 'summer wedding' no results show.
I am using this filter criteria on another site and it works fine. Is there a setting I need to set for it to work. The search is using drupal default search index and the block with this search is also working fine. How to I make it work on the view for more then one word?

#9 views_search_index_multiple_terms-1948510-7676455.patch888 bytespdcarto
PASSED: [[SimpleTest]]: [MySQL] 1,658 pass(es). View
Members fund testing for the Drupal project. Drupal Association Learn more


alanpeart’s picture

Same problem here. No solution found yet but will post if I find it. Same search works fine using Drupal default search but fails when using views keyword search.

alanpeart’s picture

It appears that views is incorrectly adding a Group By clause to group on the "score" field when using this filter. I can't find where this is happening, since it is not visible at any of the normal points where I might debug a views query, such as hook_views_query_alter() or hook_views_pre_execute(). Also, since it's no longer possible to modify the raw SQL of a views query, I can't just remove it using a string replacement which was one of my moves of desperation in Drupal 6.

I'll keep trying to track it down but it would make my day if a views expert could jump in and identify how this extra grouping field is getting added.

My query as shown in views preview is:

SELECT node.nid AS nid, node.title AS node_title, SUM(search_index.score * search_total.count) AS score, 'node' AS field_data_field_nhs_private_node_entity_type, 'node' AS field_data_body_node_entity_type
{node} node
LEFT JOIN {search_index} search_index ON node.nid = search_index.sid
LEFT JOIN {search_total} search_total ON search_index.word = search_total.word
INNER JOIN {field_data_field_internal_external} field_data_field_internal_external ON node.nid = field_data_field_internal_external.entity_id AND (field_data_field_internal_external.entity_type = 'node' AND field_data_field_internal_external.deleted = '0')
WHERE (( (node.status = '1') AND( (search_index.type = 'node') AND( (search_index.word = 'mental') OR (search_index.word = 'health') ))AND (field_data_field_internal_external.field_internal_external_tid = '32') ))
GROUP BY search_index.sid, score, nid, node_title, field_data_field_nhs_private_node_entity_type, field_data_body_node_entity_type
HAVING (( (COUNT(*) >= '2') ))
ORDER BY node_title ASC

The part that is added that is messing up the results is the "score" field in the GROUP BY clause. There's no reason to group by this field since it's involved in a SUM expression in the SELECT clause.

alanpeart’s picture

OK, I think I have found the problem. The bug is in views/modules/search/views_handler_filter_search.inc on line 153:

$this->search_score = $this->query->add_field('', "SUM($search_index.score * $search_total.count)", 'score', array('aggregate' => TRUE));

There are actually two bugs here for the price of one, but you don't find the second until you resolve the first! First of all, the 3rd variable being sent to add_field is the name of the field - in this case, because it's a formula this name, 'score', is an alias. However, this causes a problem for the default views query plugin, because there's already a field in search_index called 'score'. Because this query has aggregation, it thinks it's being asked to group on the 'score' field in search_index, so it throws the wrong results.

I tested this, eliminating the naming confusion by changing the line to:

$this->search_score = $this->query->add_field('', "SUM($search_index.score * $search_total.count)", 'aggscore', array('aggregate' => TRUE));

This is when I encountered the second bug. I received the error message "SQLSTATE[42000]: Syntax error or access violation: 1056 Can't group on 'aggscore'".

Eventually I tracked this down to the function compile_fields in views_plugin_query_default.inc. It turns out that if a field does not have a table name associated with it, it is assumed that it is a formula, and it is furthermore (incorrectly I believe) assumed that grouping is required for this field. So it tries to group on our field that already has a SUM operation being performed.

The simple workaround to fix this behaviour is to add a table name when adding the field. We have two tables in the formula so let's pick one:

$this->search_score = $this->query->add_field('search_total', "SUM($search_index.score * $search_total.count)", 'aggscore', array('aggregate' => TRUE));

Now the field is identified correctly as one that should be excluded from grouping. This fixed my problem. It seems to me that a patch would be useful here but honestly, I'm not sure if fixing this one line of code is the correct way of resolving the underlying problem. Very interested in anyone else's comments and whether this fix also resolves the OP's issue.

LTech’s picture

Thanks so much. Your code fix worked for me.

LTech’s picture

Status: Active » Closed (fixed)
LTech’s picture

Status: Closed (fixed) » Active

I've re-opened this bug because I am still having issues with it.
I have a painting called 'with an open heart' on my site. If I search for 'with an open heart' nothing comes up but if I search for 'with open heart' something comes up.
I want to use the drupal main search filter with my view. I thought search:serach terms filter was the main search index but I am getting different results eg 'with an open heart' works on the main search filter for the site.
How do I use the main search filter but be able to display the results in a nice grid and have extra filters for advanced searching - like I can built easily with a view?
Thanks so much

LTech’s picture

Status: Active » Closed (works as designed)

It seems one of my other filters was not allowing the search:search term filter to work correctly. On a fresh view it works as designed.

pdcarto’s picture

For anyone who doesn't want to hack views or wait for this to be fixed in a release, this can be fixed with a hook_views_query_alter function as follows (be sure to change the function name to reflect the name of the module you are putting it in, save, and clear cache):

 * Implements hook_views_query_alter
 * @param type $view
 * @param type $query
function mymodulename_views_query_alter(&$view, &$query) {
  // Fix bug where multiple terms in search index filter returns no results
  if (isset($query->relationships['search_total']) 
      && !empty($query->fields['score']) 
      && empty($query->fields['score']['table'])) {
    $query->fields['score']['table'] = 'search_total';
pdcarto’s picture

Status: Closed (works as designed) » Needs review
888 bytes
PASSED: [[SimpleTest]]: [MySQL] 1,658 pass(es). View

And here's a patch against current 7.x-3.x-dev. I found that it was unnecessary to rename 'score' as 'aggscore'. Simply substituting 'search_total' for the blank table name was sufficient.

jjchinquist’s picture

Version: 7.x-3.6 » 7.x-3.x-dev
Status: Needs review » Reviewed & tested by the community

A big thank you on that patch. It works for me and still applies cleanly to the newest DEV version.

Tested OK on our DEV server copy (1::1 mirror of the prod server). I do not see any issues. We plan to install views-dev and this patch on the production site tonight to fix the issue.

Please apply the patch. Thanks.

rickmanelius’s picture

The patch in #9 applied cleanly but did not provide the intended functionality for 7.x-3.7. I realize that #9 and #10 state this is against the -dev branch, so in the meantime I'll continue to use the quick fix in #8, which works for the stable version.

Looking forward to seeing this committed!

jjchinquist’s picture

There have been many commits to views since the last release. It cannot be used on that version.

rogernyc’s picture

Guess maybe this has been addressed in Views 7.x-3.7 ?
Checking 'Remove search score' in my 'Search: Search Terms' exposed filter got things working with 2 or more search terms...

phenaproxima’s picture

Issue summary: View changes

The fix in #8 worked for me with 7.x-3.7.

RoloDMonkey’s picture

studiozut’s picture

I tossed the code from pdcarto's #8 above into a module: https://github.com/gwu-libraries/views-patch (attributed in the .info)

I'm on drupal 7.27 with views 7.x-3.8, seems to have improved my results, but they still don't match the default site search and now getting this when I run cron:

Notice: Uninitialized string offset: 0 in search_index() (line 604 of /var/www/modules/search/search.module).
Notice: Undefined index: in search_index() (line 629 of /var/www/modules/search/search.module).

RoloDMonkey’s picture

Status: Reviewed & tested by the community » Needs review

Based on the information in #16, I think this needs more review.

happysnowmantech’s picture

Status: Needs review » Closed (duplicate)

Closing as a duplicate of Search terms: nodes dont appear when using multiple arguments. That issue also has some additional patches.

CD’s picture

Thanks rogernyc,

This just got it working for me too:

Guess maybe this has been addressed in Views 7.x-3.7 ?
Checking 'Remove search score' in my 'Search: Search Terms' exposed filter got things working with 2 or more search terms...

On Views 7.x-3.13

jebeze_alexander’s picture

Advice in #13, checking 'Remove search score' in my 'Search: Search Terms' exposed filter fixed the issue for me as well.

Drupal 7.23; Views 7.x-3.7, and also using Better Exposed Filters 7.x-3.2.