Alter a View query to add a WHERE clause with OR group operator
For a specific website, I had to alter the query run by Views 2 in order to extend the search feature of the project issue. For one it's a good thing that project issue now uses Views, I'm not sure I would have been able to achieve this otherwise.
Here is the scenario. I wanted to be able to run a search through the issues as usual, but the core search index only includes the full words. In my case, having urls as title of the issues, I wanted to be able to query issues on specific domain. For example, an issue titled
http://sub.domain.com/
will be indexed subdomaincom. The only way to get this issue in the results is to type the full sub.domain.com. Quite problematic if you want to retrieve all the issues from a given domain name and include all its subdomain.
A workaround is to use directly SQL with a query containing WHERE (node.title LIKE '%keyword%'), with an OR operator with the rest of the WHERE clause generated by Views. After much dprint_r() and Views API reading, the piece of code to achieve what I wanted to do is pretty low:
<?php
function swbugs_views_query_alter(&$view, &$query) {
// Only apply this where clause on the search issue project view.
if ($view->name == 'project_issue_project') {
// Get the keyword used for the search.
$text_query = isset($_GET['text']) ? $_GET['text'] : '';
// Add a new group to the query with an OR group operator.
$view->query->add_where('swbugs_group', "node.title LIKE '%%%s%%'", $text_query);
$view->query->set_group_operator('OR');
}
}
?>
Add new comment