TLDR: It’s not that hard to get Views to show the results of SQL expressions as if they were normal field values. Using SQL expressions pulls results from the database faster, more effectively, and more safely than using the Views PHP or Computed Fields modules.
By way of background, we’ve been working pretty hard on a complicated site that requires a lot of number-crunching. In order to make the site more responsive to users, we’re storing calculation results in a custom table, from which they can be pulled quickly. And we want to use those results in Views.
While there are interesting discussions to be had about whether it’s always a good idea to use Views (see Building Energy.gov without Views, and Understanding Views versus custom code) it really can’t be beat for rapidly iterating over different designs — adding fields, fiddling with their presentation, what have you. So we did a little coding, and exposed the custom table to Views using hook_views_data(). All of this is reasonably straightforward (for a given value of straightforward), and there are any number of guides out there for creating custom tables in Drupal 7 and exposing their fields to Views. This post is not about that.
We wanted to show a value in a View that could be easily computed from other values in our custom table. But for computed values, the options are pretty limited:
- Avoid the problem by adding another field to the custom table to store the calculated result. Perfectly reasonable, but not optimal in our use case.
- Use Views PHP. Ugh, no. I really do not like storing code in the database – bad security, poor performance.
- Use Computed Field. Incomplete Views support, and, like Views PHP, uses eval(). Can’t sort on the computed field unless the result is stored, inefficiently, in the database. Again, no.
- Get the database to do the work using an SQL expression.
There are some nice things about option #4. MySQL and its competitors are really, really fast – much faster than PHP. Also, they are quite happy to sort records by the value of an SQL expression. So if we can persuade Views to use an SQL expression as if it were a field, all of the normal Views sorting options become available, as well as the normal formatting options.
How do you do this? Well, there are two ways. You could use the Views Raw SQL module, which may be fine in the simple case. I looked at it, and it just made me nervous – from a security perspective it’s not much different from storing PHP code in the database. So I wrote a custom views field handler.
Here’s the code for the handler:
class MYMODULE_views_handler_field_expression extends views_handler_field {
var $expression_alias = 'unknown';
var $expression = NULL;
/**
* Construct a new field handler.
*/
function construct() {
parent::construct();
if (!empty($this->definition['expression'])) {
$this->expression = $this->definition['expression'];
}
if ( !empty($this->definition['expression_alias']) ) {
$this->expression_alias = $this->definition['expression_alias'];
}
}
/**
* Called to add the expression as a field to a query.
* Overrides and based on views_handler_field->query().
*/
function query() {
// Make sure the table is available, and get the alias.
$table_alias = $this->ensure_my_table();
// Boilerplate from views_handler_field->query().
$params = $this->options['group_type'] != 'group' ? array('function' => $this->options['group_type']) : array();
// Insert the table alias into the expression and its alias, and use the add_field method of the underlying query object.
// See add_field() in views_plugin_query_default.inc
$this->field_alias = $this->query->add_field(NULL, str_replace('ALIAS', $table_alias, $this->expression), $table_alias . '_' . $this->expression_alias, $params); // More boilerplate.
$this->add_additional_fields();
}
}In case you are not familiar with creating views handlers, this code should go into a file named MYMODULE_views_handler_field_expression.inc. The file should be in the same directory where your MYMODULE.views.inc file sits, and it needs to be listed in your module’s .info file.
The handler gives Views the ability to treat an SQL expression as a normal field as a general matter, but you still have to tell Views where and how to use it. That happens in code as well. In our case, the number we wanted to show in our Views could be expressed in SQL roughly like this:
ROUND(TABLE_ALIAS.value – (IFNULL(TABLE_ALIAS.shares,0)*IFNULL(TABLE_ALIAS.price,0)),0)
So here’s how we turn that expression into a field on our custom table, using our new custom field handler. We go into our existing implementation of hook_views_data() and add a new field:
function MYMODULE_views_data() {
$data = array();
… $data['MYTABLE']['MYNEWFIELD'] = array(
'title' => t('TITLE FOR MYNEWFIELD'),
'help' => t('DESCRIPTION OF MYNEWFIELD'),
'field' => array(
'handler' => 'MYMODULE_views_handler_field_expression',
'click sortable' => TRUE,
'expression' => 'ROUND(ALIAS.value - (IFNULL(ALIAS.shares,0)*IFNULL(ALIAS.price,0)),0)',
'expression_alias' => 'MYNEWFIELD',
),
'filter' => array(
'handler' => 'views_handler_filter_numeric',
'allow empty' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort',
),
);…
return $data; }
Clear caches and you will now see that MYNEWFIELD is available in any View that includes your custom table. With the handler in hand, you could also use hook_views_data_alter() to add custom computed fields to existing tables.
Have fun!