Views 'Search terms' exposed filter not working as expected

Question

I have a view that displays a list of people. I want to allow the list to be filtered by its search terms, so I've added a 'Search terms' exposed filter. I'm using hook_form_alter() to override this exposed filter so the user can select from a list of countries, so they can "Search by country". This just passes a country name in as the exposed filter value. I've added a second 'Search terms' exposed filter so a user can further filter by keyword.

This doesn't seem to work as expected. When I choose 'Australia' from the first filter, I get 39 results, which is correct. When I then enter 'Astronomy' into the second filter, I get no results. I expected to get back 1 result, a user whose node contains both 'Australia' and 'Astronomy'. If I leave one filter blank and enter 'Australia Astronomy' into the other, I get the correct user result.

Is there a way to make this work the way I want to? The first exposed filter has to be filtering on a node's indexed content (find country mentioned anywhere in the node), so I can't filter on a single field here. Thanks in advance!

Answer 1

The mistake comes from the WHERE conditions :

AND(( (search_index.type = 'node') AND( (search_index.word = 'australia') ))AND( (search_index.type = 'node') AND( (search_index.word = 'astronomy') ))))

This will always return empty result ! Not convinced? simply try

    SELECT * FROM `search_index` WHERE `word` = 'Australia' AND `word` = 'Astronomy'

So you'll need a subquery here : for the sake of example, let's say :

SELECT node.title AS node_title, node.nid AS nid
FROM node 
LEFT JOIN search_index ON node.nid = search_index.sid
WHERE node.status = '1' 
AND  search_index.word = 'Astronomy'
AND sid IN ( 
    SELECT sid FROM `search_index` WHERE `word` = 'Australia' 
)
LIMIT 0 , 30

Now to get it I suggest that you implement hook_views_query_alter(&$view, &$query). First, call Views API :

/**
 * Implementation of hook_views_api().
 */
function YOUR_MODULE_views_api() {
  return array(
    'api' => 3, //Views 3
    'path' => drupal_get_path('module', 'YOUR_MODULE'),
  );
}

And then the tricky part

function YOUR_MODULE_views_query_alter(&$view, &$query) {
  if($view->name == 'YOUR_VIEW' && (count($view->YOUR_EXPOSED_FIELD) > 1 ) )  {
    dpm($view); //OR dpm($query); 
    //alter your query here ; you'll have to debug and tune the following lines
    //short one
    $query->where ... = ...
    //alternative one
    foreach($query->where as &$condition_group) {
      foreach($condition_group['conditions'] as &$condition) {
        //not sure from here, use `dpm ()` on your own objects            
        if($condition['field'] == 'search_index.word') {
          $condition = array(
            'field' => 'search_index.sid',
            'value' => db_query('SELECT sid FROM `search_index` WHERE `word` = '.$view->YOUR_EXPOSED_FIELD),
            'operator' => 'IN',
          );
        }
      }
    }
  }
}

This hook should be placed in MODULENAME.views.inc and it will be auto-loaded. MODULENAME.views.inc must be in the directory specified by the 'path' key returned by MODULENAME_views_api(), or the same directory as the .module file, if 'path' is unspecified.

EDIT : as per Vincent, used add_where_expression for the subquery and unset the original part of the query related to that search

$query->add_where_expression('3', "sid IN (SELECT sid FROM search_index WHERE search_index.word = '{$search_value}')");
//sthing like
unset($query->where[1]['conditions'][0]);

Answer 2

Apparently the Views Selective Filters module gets your "exposed filters to narrow each other down (to work as a facet search)."

Answer 3

I had a similar problem in another context with a custom entity. To avoid too much details I describe it with a common example: I needed to find nodes which referenced to node A AND node B. It was neccessary to get this by two exposed filters where the user was able to search for different search pairs like "show me nodes referencing to node B AND node A" or "referencing to node C and node E". The problem was the resulting query.

My solution was to use a second relationship (created in a custom module with function MYMODULE_views_data_alter(&$data), getting it into the view and connecting it to the second filter. I am not quite sure if that really works in your case with the Drupal search filter but hopefully it gives you a hint in a direction of a solution

Answer 4

Hey You can yourself solve this by using function hook_views_query_alter(&$view, &$query).

If i have to i will use it like in template.php of site theme:

function Yourthemename_views_query_alter(&$view, &$query)
  {
    //firstly get name by printing like print $view->name;
    //Now use condition like following
    if($view->name=='viewname')
    {
      //Now Print here $query like:-echo "<pre>"; print_r($query);
      //Their you got conditions that you have used as exposed filter in views.
      //After entering 'Australia Astronomy' in second filter checkout the condition that is printing using print_r;
      //Now Alter the condition their accoring to this.like:-

      Query->where[]='YOUR_Conditon';


    }
  }

You have to change condtions accoring to result you want.You have to try it yourself.we will solve it if we have you have provided exported view code.