Solving a VIEWS 2 problem with MAX() and GROUP BY custom SQL queries

First off, thank you to everyone who has worked diligently on developing this module. I have made some amazing use of it and still do. Version 2 was a major leap forward for me. I am looking toward using version 3 and the D7 branch soon enough.

With the pleasantries aside, here comes the fun problem I recently ran into. I should predicate this with a disclaimer: I've spent days searching the net, your issues and several docs on how to deal with the VIEWS 2 API and trying to override a native feature of the module. I've also attempted to resolve my issue by installing a swath of modules that compliment VIEWS to try and solve this problem (things like views_groupby, views_or, data and the companion schema, and a few others), all in vein. I could not get the output, essentially the SQL query to match my requirements.

To solve this problem I eventually turned to the trusted hook_views_data() and hook_views_handlers() to get what I needed. I have no problems with that. Absolutely love the API. Where things broke down were when I started to play with the $this->query->add_groupby() function.

Here is a snippet of the handler routine I created:

/**
 * @defgroup views_field_handlers Views' field handlers
 * @{
 * Handlers to tell Views how to build and display fields.
 *
 */

/**
 * Specific SLSA field handler that renders an unformatted ceiling price.
 */
class slsa_handler_field_ceiling_price extends views_handler_field {
  
  /**
   * Called to add the field to a query.
   */
  function query() {
    $this->ensure_my_table();
    // Add the field.
    $this->field_alias = $this->query->add_field(NULL, "MAX(CAST(".$this->table_alias.".".$this->real_field." AS DECIMAL(14,2))) AS " . $this->table_alias . "_" . $this->real_field);

    $this->add_additional_fields();
    
    $groupby_fields = array('uc_products_model', 'node_data_field_slsa_manufacturer_field_slsa_manufacturer_value');
    $groupby = implode(', ', $groupby_fields);
    
    $this->query->add_groupby($groupby);
    $this->query->no_aggregates_in_groupby = TRUE;
  }
  
  /**
   * Render the field.
   *
   * @param $values
   *   The values retrieved from the database.
   */
  function render($values) {
    $value = $values->{$this->table_alias . "_" . $this->real_field};
    
    return check_plain($value);
  }
}

There are several things going on here, and I'll explain what I'm doing.

Using an example I found on the web, the line $this->field_alias = $this->query->add_field(NULL, "MAX(CAST(".$this->table_alias.".".$this->real_field." AS DECIMAL(14,2))) AS " . $this->table_alias . "_" . $this->real_field); is tricking views so I can have a MAX() statement in the query. No problem here.

The $groupby_fields stuff is no problem either in terms of it working. Where I did have problems was with what this function ($this->query->add_groupby($groupby)) does.

Here is an example of what VIEWS does with this when I do not have my boolean flag in ($this->query->no_aggregates_in_groupby):

  GROUP BY uc_products_model, node_data_field_slsa_manufacturer_field_slsa_manufacturer_value, nid, node_title, node_language, uc_products_model, node_data_field_slsa_manufacturer_field_slsa_manufacturer_value, node_type, node_vid, node_data_field_slsa_product_id_field_slsa_product_id_nid, node_data_field_slsa_product_id_field_slsa_supplier_ref_nid, MAX(CAST(content_type_product_supplier_join.field_slsa_ceiling_price_value AS DECIMAL(14,2))) AS content_type_product_supplier_join_field_slsa_ceiling_price_value

The key things to remark here are the extra GROUP BY fields I do not need and the inclusion of my altered field alias. Rather, I need the GROUP BY to read:

  GROUP BY uc_products_model, node_data_field_slsa_manufacturer_field_slsa_manufacturer_value

To get this to work, I had to patch the includes/query.inc with a small conditional boolean. What I was wondering is if there was already something like this I wasn't aware of? If so, how can I accomplish this without this patch? If not, is this of any use and could we have this rolled into the next release?

Thank you.