Does Views supports MIN(), MAX() in SELECT queries?

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
prosk's picture

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

Eric At NRD's picture

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

Scott Reynolds's picture

Even a custom field handler doesn't look like it can manipulate the query object in a way that will work

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...

The table this field is attached to. If NULL, it is assumed this will be a formula; otherwise, ensure_table is used to make sure the table exists.

couldn't make it to work

siddhu151plex's picture

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

Eric At NRD's picture

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

Eric At NRD's picture

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',
      ),
    ),
  );
}
?>

Views Developers

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: