# Views 'Search terms' exposed filter not working as expected

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!

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]);  • This looks like it's on the right track - I will need to review. Does this look like a bug? I had opened an issue in the Views issue queue on Drupal.org as well. – Vincent Jul 21 '14 at 14:29 • not at all. Views simply doesn't manage sub queries out of the box. There was an interesting post about that from merlinofchaos or another Drupal team guy, but I don't find it to send you the link now. – Kojo Jul 21 '14 at 14:43 • Ok, I dpm()ed into this and the problem I originally ran into is that $condition['field'] is a DatabaseCondition object. I'm not sure how to get into it to modify. – Vincent Jul 21 '14 at 15:48
• $conditions is protected but conditions() isn't...try$conditions = &$condition->conditions(); and manipulate them directly ;) – Kojo Jul 21 '14 at 15:52 • Ok, I'm close! Here's a screenshot of what I see for: gist.github.com/vinmassaro/e04eb1181824561b3f35 – Vincent Jul 21 '14 at 18:55 Apparently the Views Selective Filters module gets your "exposed filters to narrow each other down (to work as a facet search)." • Thanks, but this module is about reducing the results displayed in an exposed filter. In my issue, I am having a problem with the query being sent. – Vincent Jul 16 '14 at 14:35 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

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.