Using hook_form_alter and hook_views_query_alter to change a query before execution

One of the things that makes Drupal so dynamic and extensible is it's system of hooks.  Hooks are a way for one module to alter or extend the functionality of another module.  There are "core" hooks that allow the user to change the behavior of core modules, such as user, node, and taxonomy.  However, contributed modules also sometimes offer hooks that your module can take advantage of.  The Views module is a perfect example.

I recently ran into some trouble trying to construct a view with exposed filters.  I (in D7), was using the Data module to construct a view of a custom table from the database.  For anyone that doesn't know, the Data module gives you the ability to "adopt" custom tables from your database.  A custom table is a table that you add directly to your MySQL database, either through custom sql commands, manually through phpmyadmin, or imported from somewhere else.  Once the table is adopted, you can use Views to display, sort, filter the information in that table.  Unfortunately, Views doesn't work as smoothly with these adopted tables as it does with Drupal entity types.  At least not out-of-the-box.  But with hooks, we can change the Views module's behavior to suit our needs. 

Let me explain be describing the problem I was having.  As I mentioned above, I was trying to create an exposed filter that would allow the user to choose values with which to filter the result set, except from a select list.  However, by creating the exposed filter from within the View interface, I was only given an option to use a text field for the particular field that I was trying to filter on.    Luckily, there is a core hook that gives us the ability to modify the filter (which is a form) before it is rendered, hook_form_alter().

hook_form_alter()

This hook looks like this: hook_form_alter(&$form, &$form_state, $form_id).  Since we are actually modifying the form, the parameter that we care about is $form.  $form is a nested array of form elements that comprise the form.  To get a better idea of what we're working with, dpm (download the Devel module http://drupal.org/project/devel and learn about dpm) the $form variable.  The following is what dpm($form) looks like in my case.

As you can see, the filter that I'm trying to change (TicketStatus) is a keyed array, and the #type key has a value of 'textfield'.  To make the filter a select list, I have to change #type to 'select'.  I also have to provide an array options from which to select, and I have to specify that the user can select multiple values.  The following is what my custom module looks like:

 

function MY_MODULE_form_alter(&$form, &$form_state, $form_id) {
    if($form['#id'] == 'views-exposed-form--FORM-NAME') {  
        $options = array(
            'Resolved' => 'Resolved',
            'New' => 'New',
            'Open' => 'Open',
            'Stalled' => 'Stalled',
        );
 
        $form['TicketStatus']['#type'] = 'select';
        $form['TicketStatus']['#options'] = $options;
        $form['TicketStatus']['#default_value'] = array();
        $form['TicketStatus']['#multiple'] = True;
        $form['TicketStatus']['#size'] = 4;
    }
}
 
It worked!  The exposed filter is now a select list!  But......multi-select doesn't work :(  Well, it works as far as the form is concerned, in that I can actually select multiple values in the list.  However, if I select multiple values and then press Apply, I always get no results.  This is because the sql query that Views is using needs to be modified to take into account that there multiple values.  Here we can use a Views hook called hook_views_query_alter().
 

hook_views_query_alter()

This hook looks like this: hook_views_query_alter(&$view, &$query).  Here, we are going to modify the query, which is an element of the $query variable.  Let me show my implementation of the hook first:

function MY_MODULE_views_query_alter(&$view, &$query) {
    if($view->name == 'MY_VIEW' && (count($view->exposed_raw_input['TicketStatus']) > 1 ) ) {
        foreach($query->where as &$condition_group) {
            foreach($condition_group['conditions'] as &$condition) {
                if($condition['field'] == '_last_activity.TicketStatus') {
 
                    $condition = array(
                        'field' => '_last_activity.TicketStatus',
                        'value' => $view->exposed_raw_input['TicketStatus'],
                        'operator' => 'IN',
                    );
 
                }
            }
        }
    }
 
Ok, so.  The first thing that I do is make sure that I have the right view, and that the exposed_raw_input['TicketStatus'], which is an array, has more than one item.  The following is part of a screenshot of dpm($view):
 
 
As you can see, exposed_raw_input['TicketStatus'] has two key=>value pairs.  This means that the TicketStatus exposed filter had two values selected when the user pressed Apply.  
 
Next, I'm gonna dig into the query to change it.  But first, let's take a look at the way Views constructs the query by default.  You can ask Views to display the sql query that it uses by going to /admin/structure/views/settings.  This is what it looks like when I select multiple values:
 
 
Since I selected two values in the filter, Views appends them together inside of the WHERE clause.  But this is the wrong syntax!  It should be:
WHERE (((_last_activity.TicketStatus IN ('New', 'Open'))))
Let's take a look at how the WHERE clause is constructed within the $query variable.  Here is dpm($query):
 
 
The WHERE clause is defined by the $query['where'] nested array.  So, in my hook I iterated through the where[ ]['conditions'] arrays and searched for the one with a value of _last_activity.TicketStatus for the key #field.  As you can see above, the #operator key has a value of LIKE, but we need it to say IN.  Simple, recreate the ['conditions'] array with all of the same values, except 'operator'=>'in' - as I did in my hook.
 
 Now, when the user selects multiple values and presses Apply, the correct values will be used in the query, and the correct syntax will be applied.