SQL Views in Drupal Views: Rounding Custom Numeric Fields

Drupal Views can access more than just the nodes in your Drupal system, with just a little bit, I mean a teeny-tiny bit, of coding in a minimalist custom module you can expose all manner of database tables and SQL views to Drupal Views.

The Basics
The Logrus documentation has a “Node Example” module doc (http://views-help.doc.logrus.com/help/views/api-example) that shows all manner of programmatic access to Views power, namely showing you how to connect a non-Drupal table to the Node table, and equip Views to query and display this data. This gets you all you need to know in order to connect a table, and by extension, you can employ this same methodology to SQL views. Since this is not an attempt to duplicate the Logrus doc, I will leave out the parts on relating this to nodes, and for brevity I will leave out writing an SQL view, but I will note: make sure that the Drupal user has SELECT privileges on your SQL view.

Numeric Types
The “Node Example” shows how to do basic settings in hook_view_data() to tell Drupal about a standard string column. The example has the basic info to expose a varchar type column named “item_name” from an SQL view named “test_view_sql”.


function customSQLViews_views_data() {
$data['test_view_sql']['item_name'] = array(
'title' => t('String Field'),
'help' => t('String Field.'), // The help that appears on the UI,
'argument' => array(
'handler' => 'views_handler_argument_string',
),
'field' => array(
'handler' => 'views_handler_field',
'click sortable' => TRUE,
),
'filter' => array(
'handler' => 'views_handler_filter_string',
),
'sort' => array(
'handler' => 'views_handler_sort',
)
);

What it doesn’t show is how to do a numeric column, however, with a little bit of guesswork (or searching) makeing a basic numeric column is trivial. All you need to do is to use the numeric equivalents for the argument, field, and filter settings.


function customSQLViews_views_data() {
...
$data['test_view_sql']['item_value'] = array(
'title' => t('Numeric Field'),
'help' => t('Numeric Field.'), // The help that appears on the UI,
'argument' => array(
'handler' => 'views_handler_argument_numeric',
),
'field' => array(
'handler' => 'views_handler_field_numeric',
'click sortable' => TRUE,
),
'filter' => array(
'handler' => 'views_handler_filter_numeric',
),
'sort' => array(
'handler' => 'views_handler_sort',
)
);

Advanced Numeric Formatting
This is all well and good and now your view will be able to be rendered in Drupal Views, but it will NOT be able to round. For this, you need to tell the “handler” that it is more than just numeric, that it is “float”.


function customSQLViews_views_data() {
...
'field' => array(
'handler' => 'views_handler_field_numeric',
'click sortable' => TRUE,
'float' => TRUE, // enables advanced formatting options
),
...

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s