Views is an amazing module, but sometimes you can come across certain limitations that make life difficult. For example, I created a view that pulled in a bunch of nodes based on different taxonomy terms. The problem was that I had more than one taxonomy term in different vocabularies being used to filter the results, which essentially caused the same field in the term_data table to be used for both taxonomy filters. So, no matter what I changed the parameters to I always received no results. I did try and add the second term as an argument but it isn't possible to do LIKE matches with views arguments.
After a bit of head scratching I decided that I needed to create a custom Views filter for the second element. This would create a second join between the node and the term_data tables and allow me to filter it using a LIKE match. Using this approach made sense as I also wanted the element to be exposed so that a user could enter a search term to restrict the View results. What follows is how to go about setting up custom Views filters along with the associated exposed form elements by using the Views API.
The first thing we need is a implementation of HOOK_views_api() so that Views knows that the module is Views enabled. These function hooks need to be added to a module so you can either use an existing module or create a new one.
/**
* Implentation of HOO_views_api()
*
* @return array An associative array of views options.
*/
function mymodule_views_api() {
return array(
'api' => 2.0
);
}
Before we can point to our Views filter handlers we must first let Views know where they are by including a HOOK_views_handlers() hook. Here, we are defining a single handler class that will make the needed changes to our query object and add in the form element. Note that the path is set to a directory called views in the current module.
/**
*
* Register the views handlers with the view.
*
* @return array An associative array of options.
*/
function mymodule_views_handlers() {
return array(
'info' => array(
'path' => drupal_get_path('module', 'mymodule') . '/views',
),
'handlers' => array(
'mymodule_special_data_filter_view' => array(
'parent' => 'views_handler_filter',
),
),
);
}
The next thing we need is an implementation of HOOK_views_data(), which will tells Views about all sorts of things, but in this case we will tell Views about our filter. The function should return an associative array of items, each one being a filter that we want to implement. The array can be very large and complex, with table and field definitions, but to keep things simple I have tied in this filter to the node table by using the 'node' element of the $data array. This filter will therefore appear when we go on to create a node View type and will appear in the list of node filters.
The important bit here is the filter element (which tells Views to define a filter) and the filter handler (which will handle what our filter does). The handler element contains the name of a class we created a reference to earlier that will add the extra joins and where clauses to the query.
/**
* Implementation of HOOK_views_data
*
* Tells views about the filters we need to add.
*
* @return array An associative array of options.
*/
function mymodule_views_data() {
$data = array();
$data['node']['mymodule_special_data'] = array(
'title' => t('Custom Taxonomy Lookup'),
'help' => t('Added to create a second link between the node and taxonomy tables.'),
'filter' => array(
'handler' => 'mymodule_special_data_filter_view',
),
);
return $data;
}
Create a file called mymodule_special_data_filter_view.inc in your module directory in the views sub folder and define a class called mymodule_special_data_filter_view. This class extends views_handler_filter and defines two methods. The value_form() method is used to create the element for the exposed form widget. The query() method is used to add (or remove) parts of the query in the View.
ensure_my_table();
// Rest of query information goes here
}
}
Essentially, what we are trying to add to the query are the following clauses.
A left join between the term_node and the node tables, using the alias term_node2 for the term_node table.
LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid
A left join between the term_data and the term_data tables, using the alias term_data2 for the term_data table.
LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid
An addition to the WHERE clause to restrict the data being included.
AND (UPPER(term_data2.name) LIKE UPPER('%Term Name%'))
Although it took me a while to actually track down this information, the easiest part of this is creating the form element for the exposed filter. All you need to do is add a method called value_form() and then define the form elements you want to have in your exposed form. The following will create a single text field that we can use to extract our data from.
class mymodule_special_data_filter_view extends views_handler_filter
{
/**
* Shortcut to display the exposed options form.
*/
function value_form(&$form, &$form_state) {
//$options = menu_parent_options(menu_get_menus(FALSE), 1);
$form['value'] = array(
'#type' => 'textfield',
'#title' => t('Term Name'),
'#default_value' => NULL,
);
return $form;
}
... SNIP
The final part of this is to add the code to the query() method to change the outputted SQL. I have included lots of comments in the code to clearly explain what is going on. Essentially, if the exposed form has been entered by the user then the value is picked up by this code and the needed joins are added. I also found that I had to add a call to $this->query->add_table() in order to create an alias for the term_node table. If this is left out then the second join is done between term_data2 and term_node, which leaves us back at square one.
function query() {
// Ensure table alias has been set
$table_alias = $this->ensure_my_table();
// Get term value
$term_value = check_plain($this->view->exposed_input['mymodule_special_data_filter_view']);
// Make sure term value is set.
if ($term_value != '') {
/*
Query parts to add:
1. LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid
2. LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid
3. AND (UPPER(term_data2.name) LIKE UPPER('%Term Name%'))
*/
// Add the term_node table with an alias of term_node2 this ensures
$this->query->add_table('term_node', NULL, NULL, 'term_node2');
// Add a join between the term_node and the node tables
// This uses the alias term_node2 for the term_node table
$join = new views_join();
$join->construct('term_node', $this->table_alias, 'vid', 'vid', array(), 'LEFT');
$this->query->ensure_table('term_node2', $this->relationship, $join);
// Add a join between the term_data and term_node tables
// This uses the alias term_data2 for the term_data table
$join = new views_join();
$join->construct('term_data', 'term_node2', 'tid', 'tid', array(), 'LEFT');
$return = $this->query->ensure_table('term_data2', $this->relationship, $join);
// Add a where clause to the query
$this->query->add_where($this->options['group'], "(UPPER(term_data2.name) LIKE UPPER('%%%s%%')) ", $term_value);
}
}
I have added a check here to make sure that a value exists from the exposed filter for me to use, if it is then I do the query altering, otherwise the code is not run. This might be different for your implementation so you need to check what is going on around this line:
$term_value = check_plain($this->view->exposed_input['mymodule_special_data_filter_view']);
I should note that this code was created specifically for my needs and I include it here as it took me a while to find out about all the needed components. Your Drupal project will almost certainly be different from mine so this code might not do what you want.
?>
Asif