How to make one dropdown exposed filter (constrained list e.g. options) to search across in two fields (identical lists) in drupal 7 views

Question

Help! I've done a lot of research and looking for answers, both here and elsewhere but the solution to my particular problem has so far eluded me.

I'm building a search in drupal views to return users who meet certain criteria. I have the search currently doing most of what I need (proximity searching, combined name searching etc.), but I am looking for one last thing:

Each user has a list of activities they can do that they are certified to do, and a separate list of activities they can do, that they are NOT certified to do.

Both lists are constrained lists (e.g. a list of options) and both lists are identical.

e.g. List A [drive, ski, skate, fly], List B [drive, ski, skate, fly].

Normally searchers are only interested in searching for users in List A. My current search does this fine.

What I would like is an "Include Non-Certified" checkbox that when checked, uses the exposed dropdown filter for List A, and returns users who have the selected item in List A OR List B.

I have found lots of methods for searching multiple fields from one exposed filter, but they all apply to string / text entry searches, NOT to a dropdown list of options type filter.

I've looked at things like: "Global: Combine fields filter (exposed)", or the "Better Exposed Filters Drupal Module" and "Views filters populate Drupal module" but I can't see a way to get them to do what I want.

Any help would be much appreciated. I understand it seems to be a fairly unusual requirement. I also hope the above explanation is clear.

Answer 1

I do not know of a module that will do this, but, you could create your own module and implement hook_views_query_alter to alter the query before it is executed.

Answer 2

In the hope that it helps someone else with a similar problem, I'll sketch out what I had to do to solve this using the advice of 2pha above:

1. Make a custom module (MODULENAME), install and enable it.
2. Inside the module directory make a MODULENAME.form.inc file, use this to add a checkbox to the form. This checkbox will be used to toggle if we are searching only List A, or both List A & List B.

-- MODULENAME.form.inc:

<?php

function MODULENAME_form_alter(&$form, &$form_state, $form_id) {
}

function MODULENAME_form_views_exposed_form_alter(&$form, &$form_state, $form_id) {

  // Add a checkbox to search form.
  $form['togglecheckbox'] = array(
    '#type' => 'checkbox',
    '#title' => t("Include List B"),
    '#required' => FALSE,
  );
}

--

1. Make a MODULENAME.views.inc file. This will check the state of the checkbox and change the search query to include List B as needed.

Because my search is more than just List A (or List A or List B), I add a bunch of extra filters to the new query also.

-- MODULENAME.views.inc:

<?php

function MODULENAME_views_query_alter(&$view, &$query) {

    // Uncomment the below line to get the query object via devel module debug
    // dpm($query); 

    // If the checkbox is checked, then we want to use the exposed primary key also as the secondary key
  if ($view->name == 'VIEWNAME' && ($view->exposed_raw_input['togglecheckbox'] == '1')) {

        // Set variable to primary key
        $primary_key = ($view->exposed_raw_input['field_LIST_A']);

        // Add table to query, since it doesn't seem to automatically be done to exposed filters unless their value has been changed from the default (ALL for selection lists etc.)

        $query->add_table('field_data_field_LIST_B', 'users');

        $view->query->set_group_operator('OR'); // Show results that match group 1 (normal) or group 2 (added)

        // Add secondary filter to search query
        $query->add_where(2,'field_data_field_LIST_B.field_LIST_B_value',$primary_key,'=');

        // Fill in other filters, e.g. Must be valid member, must be ok for directory
        // User must be valid
        $query->add_where(2,'users.status',0,'<>');
        // The below key name is mine, you'll need to change to yours or delete this line.
        $query->add_where(2,'field_data_field_list_in_directory.field_list_in_directory_value',1,'=');

}

I struggled a bit with the naming conventions, used a lot of dpm(); debug calls (via devel module) to constantly check on variables.

I also call dpm($query); which lists the entire query structure, so you can explore it and help figure out what's going wrong and what things are called.

1. Finally you need a MODULENAME.module to initialise both the above.

-- MODULENAME.module:

<?php

function MODULENAME_hook_info_alter(&$hooks) {
  $hooks['form_alter']['group'] = 'form';
}


function MODULENAME_views_api() {
  return array('api' => 2.0);
}

--

Good luck!