Views : how to combine fields in exposed filter using is between operator

Question

In a view showing commerce products, with exposed filters, I need to make a slider filter for commerce_price field. This is easy with exposed filters (is between operator) and BEF module, but ...

commerce_price field is retrieved in the view by its relationship to Product variation type, and I've got 2 different Product variation types for the same product display :

And

This means you can make a slider for each one, but this makes no sense for end-user to have 2 prices slider !

And by default Views 3.x Global: Combine fields filter doesn’t provide "is between" operator, it only works with strings and operators like equal to, begins...

How can I filter the values of both relationships in a single slider for that Price field ? Is there a Views setup way or a module to do that? If not, how can I add a custom filter to my view using API ?

Answer 1

This is a "smoke and mirrors" situation, in which you want the end user to experience one thing when the code is doing something completely different.

I would do a little bit of jQuery trickery. First, I would expose all 4 fields (the high and low for each of the 2 fields) as text boxes. Then, I would use the jQuery UI slider (which ships with Drupal Core) to update the high and low values as the user changes them. Of course, the jQuery should hide the actual text boxes as well. This will give you the effect that you are looking for.

Finally, if you wanted this to be ultra simple in the event that Javascript fails, you can use a form alter to hide one of the pairs of text boxes and then, when the form is submitted or the view loaded, populate the values for the other filter programmatically.

function MYMODULE_form_alter(&$form, &$form_state, $form_id) {
  if ($form_id == 'THE-FORM-YOU-WANT-TO-TARGET') {
    // you'll have to look through the $form array for the field you want
    // I suggest installing Devel and running dsm($form);

    $form['YOUR-FORM-ELEMENT']['#default_value'] = isset($_SESSION['max']) ? $_SESSION['max'] : 1000;
    // Do this for all the applicable form elements...

    // Finally, tell Drupal that you want to see the form when it is submitted.
    array_unshift($form['#submit'], 'MYMODULE_filter_submit');
  }
}

function MYMODULE_filter_submit($form, &$form_state) {
  $_SESSION['max'] = $form_state['values']['YOUR-FORM-ELEMENT'];
}

You'll have to double-check to see what the variables actually look like. I suggest installing the Devel module and putting dsm($form); and dsm($form_state); at the beginning of the functions.

Lastly, any time that you add a hook, don't forget to clear your cache or Drupal won't find it.

Answer 2

I had a similar problem and my solution was to write a custom query that fetches distinct values from the desired tables in ascending order & populating the slider with the query's result. Here are the steps:

1. In the .module file of your custom module,

function <module_name>_preprocess_views_view(&$vars)
{
    $view = $vars['view'];
    $query = $view->query;

    //Use this to find out the machine name of view
    //drupal_set_message('<pre>' .print_r($view->name,TRUE). '</pre>');

    switch($view->name)
    {
        case '<view_name>':

        //Fetch distinct price values from the price table(s) & sort in ascending order since a slider ranges from min to max!
        $arr = db_query("SELECT DISTINCT column1_value FROM {price_table1},{price_table2} ORDER BY column1_value ASC")->fetchCol();

        //Convert associative arrays to normal arrays
        foreach($arr as $key=>$value)
            $normal_arr[] = (float)$value;

        //Pass variables from .module to .js file
        drupal_add_js(
            array(
                'views_slider' => array(
                    'arr_values' => $normal_arr
                    )
                ),
                array('type' => 'setting')
            );

         //Include appropriate .js file
         drupal_add_js(drupal_get_path('module', '<module_name>') . '<path_to_js_file_within_module>/<js_file>.js');

         break;
     }
}

2. In the .js file, populate the jQuery slider using the result of the query as reference. Cookies are used to retrieve values on the slider & its textboxes even after page load/views exposed form submit.

    function ($) {
    Drupal.behaviors.views_slider = {
        attach: function (context, settings) {
            var arr_val = Drupal.settings.views_slider.arr_values;

            var min = getCookie("price-min");
            var max = getCookie("price-max");

            //Here 'edit-field-price-value-wrapper' is the id of the BEF slider wrapper.
            //You can find this by using Firebug or similar element-inspecting browser apps.
            $("div#edit-field-price-value-wrapper div.ui-slider").slider({
                min: 0,
                max: arr_val.length-1,
                values:[min, max],
                slide: function(event, ui) {
                    if (ui.values[0] > ui.values[1])
                    {
                        return false;
                    }
                    $("#edit-field-price-slider-value-min").val(arr_val[ui.values[0]]); //Id of min textbox in BEF slider
                    $("#edit-field-price-slider-value-max").val(arr_val[ui.values[1]]); //Id of max textbox in BEF slider
                    setCookie("price-min", ui.values[0], 1);
                    setCookie("price-max", ui.values[1], 1);          
                },
                //When incorrect values are entered,this event updates the textboxes to their closest value
                change: function (event, ui) {
                    if (ui.values[0] > ui.values[1])
                    {
                        return false;
                    }
                    $("#edit-field-price-slider-value-min").val(arr_val[ui.values[0]]); //Id of min textbox in BEF slider
                    $("#edit-field-price-slider-value-max").val(arr_val[ui.values[1]]); //Id of max textbox in BEF slider
                    setCookie("price-min", ui.values[0], 1);
                    setCookie("price-max", ui.values[1], 1);
                }
            });
        }
    }
})(jQuery);

Hope this helps.