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]);