Posted by prosk on April 22, 2009 at 1:21pm
Hi,
Does views supports queries like for instance
Select node_revisions.vid, MAX(node_revisions.created) FROM node_revisions
which could return the last revision created for each node?
Thanks!
Hi,
Does views supports queries like for instance
Select node_revisions.vid, MAX(node_revisions.created) FROM node_revisions
which could return the last revision created for each node?
Thanks!
Comments
Bump
Prosk, did you ever figure out an answer to this, or does anyone else have any suggestions on where to start with something like this?
I'm trying to do something similar, but am having no luck. Even a custom field handler doesn't look like it can manipulate the query object in a way that will work. I'm currently looking at hook_views_query_alter() in hopes of coming up with a kludge.
Anyhow, any suggestions would be most appreciated.
Even a custom field handler
Often times, in the custom field handler u do $this->query->add_field(NULL, "MAX(SOME_FIELD)")
http://views.doc.logrus.com/classviews__query.html#7d073b7dde56cfb8c0e97...
couldn't make it to work
Hi Scott
I'm trying to do $query->add_field(NULL, "COUNT(SOME_FIELD)") in 'hook_views_query_alter' but I couldn't make it to work this way.
However I can see the sql query in the view reflected with that but the table result didn't update.
Am I missing anything in the hook? or am I doing it wrong?
Thanks.
My hook_views_query_alter() solution
Hi Scott,
Thanks for the info, I'm going to see what I can do with it!
I actually have something working right now using hook_views_query_alter(), but it is sort of a kludge. I'd be happy to hear what others think of this method.
The module I'm working on doesn't actually need MAX(), but uses DATEDIFF() with has a bunch of datetime items to define fields based on the time elapsed between them (same basic idea though).
Simple example:
FLOOR(DATEDIFF(user_join_date,user_birthdate)/365.25) AS user_age_at_join_calc
<?php
/**
* Implementation of hook_views_data()
*/
function EXAMPLEMODULE_views_data() {
$data = array();
// ... hook_views_data() table and field setup here ...
$data['EXAMPLECONTENT']['user_age_at_join_calc'] = array(
'title' => t('User Age at Join'),
'help' => t('Calculated from user-birthdate and user-join-date.'),
'field' => array(
'handler' => 'views_handler_field_numeric',
'click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort_numeric',
),
'filter' => array(
'handler' => 'views_handler_filter_numeric',
),
);
}
/**
* Implementation of hook_views_query_alter()
*/
function EXAMPLEMODULE_views_query_alter(&$view,&$query) {
// user_age_at_join_calc is not an actual field in a table, but is
// calculated by taking the difference between user_birthdate and
// user_join_date.
if ($query->fields['EXAMPLECONTENT_user_age_at_join_calc']) {
// The query object builds queries from arrays in the $query->fields
// property as follows:
// table.field AS alias
// We are going first going to save the table value, and reset it to ''
$table = $query->fields['EXAMPLECONTENT_user_age_at_join_calc']['table'];
$query->fields['EXAMPLECONTENT_user_age_at_join_calc']['table'] = '';
// Now we are going to rewrite the field value to calculate a value using
// the SQL DATEDIFF function
$query->fields['EXAMPLECONTENT_user_age_at_join_calc']['field'] =
"FLOOR(DATEDIFF({$table}.user_join_date,{$table}.user_birthdate)/365.25)";
}
}
?>
Custom field handler method
Well, thanks to Scott nudging me in the right direction and a fair amount of surfing through code in views/handlers/*.inc, I think I got this working basically how I wanted it using a new views handler that extends views_handler_field_numeric.
Here is the code I came up with. It needs some error checking and cleanup, but I think its the right basic idea now.
Thanks again Scott!
*** Update: I've cleaned up the EXAMPLEMODULE_handler_field_numeric_datediff handler a bit with sensible items for the option-form, basic error handling, etc.. ***
*** Update2: Commented out default sort and filter handlers.. ***
::: Example field handler -- EXAMPLEMODULE_handler_field_numeric_datediff.inc :::
<?php
/**
* @file
* Contains the views field handler: used to calculate the difference (in years)
* between two dates (datetime fields).
*/
/**
* Field handler used to calculate the difference (in years) between the
* dates specified in two datetime columns.
* Only positive years will be displayed (any years 0 or less) will result in an
* empty result.
* Definition terms:
* - datetime field1: Name of the more recent datetime column
* - datetime field2: Name of the older datetime column
* - *** all views_handler_field_numeric definition terms (float, etc.). ***
*
*/
class EXAMPLEMODULE_handler_field_numeric_datediff extends views_handler_field_numeric {
/**
* Add options.
*/
function option_definition() {
$options = parent::option_definition();
$options['positive_nonzero_only'] = array('default' => FALSE);
$options['floor'] = array('default' => FALSE);
return $options;
}
/**
* Add options to the options form.
*/
function options_form(&$form, &$form_state) {
parent::options_form($form, $form_state);
$form['positive_nonzero_only'] = array(
'#type' => 'checkbox',
'#title' => t('Positive Nonzero Only'),
'#description' => t('If checked, this field will only output if it is a positive, non-zero value.'),
'#default_value' => $this->options['positive_nonzero_only'],
);
if (!empty($this->definition['float'])) {
$form['floor'] = array(
'#type' => 'checkbox',
'#title' => t('Floor'),
'#description' => t('If checked, the FLOOR() of this value will be taken (setting the decimal parts to zero).'),
'#default_value' => $this->options['floor'],
);
}
}
/**
* Add our "calculated" field to the query
*/
function query() {
// Basic error checking to ensure datetime fields have been defined.
if (!$this->definition['datetime field1']) {
drupal_set_message(
'EXAMPLEMODULE_handler_field_numeric_datediff missing definition: "datetime field1" should point to a datetime field.',
'error');
}
if (!$this->definition['datetime field2']) {
drupal_set_message(
'EXAMPLEMODULE_handler_field_numeric_datediff missing definition: "datetime field2" should point to a datetime field.',
'error');
}
$this->ensure_my_table();
// Add a field which actually calculates the datediff
$this->field_alias = $this->query->add_field(
NULL, // No table specified because we will be adding it manually on the next line
"DATEDIFF({$this->table_alias}.{$this->definition['datetime field1']},{$this->table_alias}.{$this->definition['datetime field2']})/365.25",
$this->real_field
);
$this->add_additional_fields();
}
/**
* Render the output basically the same as views_handler_field_numeric,
* but check extra options such as positive-nonzero and floor.
*/
function render($values) {
$value = $values->{$this->field_alias};
if (!empty($this->options['set_precision'])) {
if ($this->options['floor']) {
$value = floor($value);
}
$value = number_format($value, $this->options['precision'], $this->options['decimal'], $this->options['separator']);
}
else {
$remainder = abs($value) - intval(abs($value));
$value = $value > 0 ? floor($value) : ceil($value);
$value = number_format($value, 0, '', $this->options['separator']);
if ($remainder) {
// The substr may not be locale safe.
$value .= $this->options['decimal'] . substr($remainder, 2);
}
}
if ($this->options['positive_nonzero_only'] && $value <= 0) {
return '';
} else {
return check_plain($this->options['prefix'] . $value . $this->options['suffix']);
}
}
}
?>
::: Example usage -- EXAMPLEMODULE.views.inc :::
<?php
/**
* Implementation of hook_views_data()
*/
function EXAMPLEMODULE_views_data() {
$data = array();
// ... hook_views_data() table and field setup here ...
$data['EXAMPLECONTENT']['user_age_at_join_calc'] = array(
'title' => t('User Age at Join'),
'help' => t('Calculated from user-birthdate and user-join-date.'),
'field' => array(
'handler' => 'EXAMPLEMODULE_handler_field_numeric_datediff',
'datetime field1' => 'user_join_date',
'datetime field2' => 'user_birthdate',
'float' => TRUE,
'click sortable' => TRUE,
),
// Custom sort and filter handlers need to be written as well.
//'sort' => array(
// 'handler' => 'views_handler_sort_numeric',
//),
//'filter' => array(
// 'handler' => 'views_handler_filter_numeric',
//),
);
}
/**
* Implementation of hook_views_handlers()
*/
function EXAMPLEMODULE_views_handlers() {
return array(
'info' => array(
'path' => drupal_get_path('module', 'EXAMPLEMODULE'),
),
'handlers' => array(
'EXAMPLEMODULE_handler_field_numeric_datediff' => array(
'parent' => 'views_handler_field_numeric',
),
),
);
}
?>