hook_views_query_alter : alter views queries with array keys

While building a product comparison website, I ran into a situation that required a view to sort using one of 2 possible price totals depending on a users profile: lets say "red state", "blue state". The goal was to open up the product comparison tables (order, fields, field names, and filtering questions and all), as well as the underlying data to this companies' staff, so a homebrew wasn't an option (which it shouldn't be, almost 99% of the time).

Luckily, there were only two possible prices for those products, so all I had to do was create two CCK *decimal* fields [ this is important, because text, and integer fields don't properly store or sort decimal points for prices ]. Then, on hook_cron, or a node submit, I updated the two totals based on other itemized fields.

However, how would the view know which column to sort products by based on a flag stored in the users session? There's about 5 ways to do that, so I went with the 1 minute solution:

Set up two sorts in a single view, and implement hook_views_query_alter(&$view, &$query)

<?php
function product_compare_views_query_alter(&$view, &$query) {
// fyi, anonymous sessions need a record in the user table of 0 to work at all....
 
if ($_SESSION['pref']['state_color'] == 'red') {
   
// we'll have to do the less popular red query
    // which's key is [1] (weighted second in the views sort order interface)
   
unset($query->orderby[0]);
  }
  else {
 
// otherwise, don't do the hill billy logic at all....
   
unset($query->orderby[1]);
  }
}
?>

Discussion

In theory, you could write a custom algorithm that built its own sort queries based on a criteria using this method. You could also probably alter filters, or fields. However, this isn't the only way to have accomplished this -- but its the easiest for me to stomach. Never use this method when existing views features, or view arguments, or even other views hooks may be the proper hammer for this nail.

If I learned anything, its that views a complex beast; flexible like F-22 -- an amazing piece of technology that offers tremendous freedom -- but often that freedom will cause you to crash in the ground if you don't think about what you are doing carefully.

Section: