Using custom, dynamic query with drupal views

Question

By dynamic I mean I want to be able to change the sql query based on user input.

Say if this is my custom query, how do I change it so that it toggles between ORDER BY .. Descending and Ascending when a user clicks the column? Is this even possible when you override the query that Views generates?

<?php
function views_views_pre_execute(&$view) {
   if($view->name=="hud_downloads") {
     $view->build_info['query']="SELECT node.nid AS nid, 
         node.title AS node_title, 
         SUM(pubdlcnt.count) AS pubdlcnt_count 
         FROM node node 
         LEFT JOIN pubdlcnt pubdlcnt ON node.nid = pubdlcnt.nid  
         WHERE (node.type in ('huds')) AND (node.status <> 0) 
         GROUP BY node.nid ORDER BY pubdlcnt_count DESC";
     }
}
?>

Answer 1

Careful, here lie dragons. Here's how I've done exactly that. First, you you create the view that displays the content of interest. Add sorts on all of the fields you are interested in (count, price, title, etc) Don't worry about them all not working well together, you'll be removing/altering them dynamically in code.

I create a module to contain a function to handle the sorting. Create an argument of type "Global:Null". It can come at any point in you're list, but there must be an argument present in that url location or the code will not execute. I usually add a title or search type. Set the argument to display all values if not present and add a validator. Choose a PHP validator and add the following.

return _mymodule_handle_sortables($view);

That will allow you to edit the contents of the function quickly/easily without having to edit view / save view / preview view at each iteration. Note that I pass the $_GET variable. That's not strictly necessary, since it should be available in the function anyway. I just do it for easier readability.

First step, get the names of the sortable fields with the devel module

function _mymodule_handle_sortables(&$view) {
    dpm($view->sort);
}

Preview the view and note the names of the fields. Once you've got them, you can do the following to alter the output of the view.

function _mymodule_handle_sortables(&$view) {
    switch ($_GET['sort']) {
        case 'sell_price_asc':
            unset($view->sort['title']);
            $view->sort['sell_price']->options['order'] = 'ASC';
            break;
        case 'sell_price_desc':
            unset($view->sort['title']);
            $view->sort['sell_price']->options['order'] = 'DESC';
            break;
        case 'alpha_asc':
            unset($view->sort['sell_price']);
            $view->sort['title']->options['order'] = 'ASC';
            break;
        case 'alpha_desc':
            unset($view->sort['sell_price']);
            $view->sort['title']->options['order'] = 'DESC';
            break;
    }
    return true;
}

Add a PHP header to your view and add the following to it

<?php echo _mymodule_sortables($_GET); ?>

Now you can dynamically display a sort header. Here's an admittedly overdone function to do that.

function _emunications_sortables($g) {
    // Collect all the relevant GET parameters
    $gopts = array();
    foreach ($g as $k=>$v) {
        if ($k == 'q') continue;
        $gopts[$k] = $v;
    }

    $opts = http_build_query($gopts);

    // Preserve the sort choice for selection
    $s1 = $s2 = $s3 = $s4 = '';

    switch ($gopts['sort']) {
      case 'alpha_asc'    : $s1 = 'selected="selected"';break;
      case 'alpha_desc'   : $s2 = 'selected="selected"';break;
      case 'sell_price_asc' : $s3 = 'selected="selected"';break;
      case 'sell_price_desc'  : $s4 = 'selected="selected"';break;
    }

    // Unset the sort option so that it can be set in the url manually below
    unset($gopts['sort']);
    $opts_sort = http_build_query($gopts);

    $output = "
    <div class='product_index_header'>
      <div class='view-selection'>
        <span class='descript'>View: </span>
        <a class='list' href='/products?$opts'>&nbsp;</a>
        <span class='bar'>|</span>
        <a class='grid' href='/products/grid/list?$opts'>&nbsp;</a>
      </div>
      <div class='sortable'>
        <select name='droppy' class='droppy kitteh' onchange=\"window.location.href=$('select.droppy').val()\">
          <option value='#'>Sort</option>
          <option $s1 value='?sort=alpha_asc&amp;$opts_sort'>a-z</option>
          <option $s2 value='?sort=alpha_desc&amp;$opts_sort'>z-a</option>
          <option $s3 value='?sort=sell_price_asc&amp;$opts_sort'>$ - $$</option>
          <option $s4 value='?sort=sell_price_desc&amp;$opts_sort'>$$ - $</option>
        </select>
      </div>
    </div>
    ";

    return $output;
}