I have a custom views field handler which is to add a MAX() field to the query. The code is as follows:
<?php
/*
* @file
* Field handler to get latest quiz result id.
*/
class quiz_views_handler_field_quiz_latest_result_id extends views_handler_field_numeric {
function construct() {
parent::construct();
// This will have to change to be set by options if the methods are ever expanded
$this->group_field = $this->definition['group field'];
}
function option_definition() {
$options = parent::option_definition();
return $options;
}
function query() {
$this->ensure_my_table();
$this->query->add_groupby("$this->table_alias.$this->group_field");
$this->field_alias = $this->query->add_field(
NULL, "MAX($this->table_alias.$this->real_field)",
$this->table_alias . '__max_resultid', array('aggregate' => TRUE)
);
}
}
and generates this SQL query:
SELECT quiz_node_relationship.child_nid AS quiz_node_relationship_child_nid, node_quiz_node_relationship.title AS node_quiz_node_relationship_title, node_quiz_node_relationship.nid AS node_quiz_node_relationship_nid, quiz_node_results.score AS quiz_node_results_score, quiz_node_results.result_id AS quiz_node_results_result_id, quiz_node_results.time_end AS quiz_node_results_time_end, quiz_node_results.nid AS quiz_node_results_nid, MAX(quiz_node_results.result_id) AS quiz_node_results__max_resultid
FROM
{quiz_node_properties} quiz_node_properties
INNER JOIN {quiz_node_relationship} quiz_node_relationship ON quiz_node_properties.vid = quiz_node_relationship.parent_vid
LEFT JOIN {node} node_quiz_node_relationship ON quiz_node_relationship.child_nid = node_quiz_node_relationship.nid
LEFT JOIN {quiz_node_results} quiz_node_results ON quiz_node_properties.vid = quiz_node_results.vid
LEFT JOIN {users} users_quiz_node_results ON quiz_node_results.uid = users_quiz_node_results.uid
WHERE (( (quiz_node_results.time_end > '0') AND( (users_quiz_node_results.uid = '1') )AND (quiz_node_properties.vid IN ('5')) ))
ORDER BY quiz_node_results_time_end DESC
The problem is that my code adds in a MAX() field to the query (which works) and the necessary GROUP BY clause but while add_groupby() is executed the clause is never added to the resulting query.
Either there's something in my code that's wrong, or there's something in the query() function of plugins/views_plugin_query_default.inc that's off, particularly the lines:
$this->has_aggregate = FALSE;
$non_aggregates = array();
list($non_aggregates) = $this->compile_fields($fields_array, $query);
if (count($this->having)) {
$this->has_aggregate = TRUE;
}
if ($this->has_aggregate && (!empty($this->groupby) || !empty($non_aggregates))) {
$groupby = array_unique(array_merge($this->groupby, $non_aggregates));
foreach ($groupby as $field) {
$query->groupBy($field);
}
if (!empty($this->having) && $condition = $this->build_condition('having')) {
$query->havingCondition($condition);
}
}
From my debug statements, I can see that my group by clause is in $this->groupby, and $non_aggregates contains the other fields that are selected out, but that $this->has_aggregate is FALSE. As a result of this being FALSE no group by clause is added to the query at all.
I don't know whether this is because of bug in my code or something in views itself.
Comments
Comment #1
gribnif commentedI also ran into this problem, in 6.x-3.0-alpha4. Furthermore, something similar is described in #1270064: Modify SQL of a view (GROUP BY) through UI and programmatically . I have posted what I believe is the start of a workable fix there.
Comment #2
linebreak commentedI ran into the same issue as Stella (with a SUM instead of a MAX) using 7.x-3.0-rc3, but I failed to adapt Gribnif's patch for 6.x-3.x-dev to the code for 7.x.
Subscribing.
Comment #3
kars-t commentedHi
I am closing this issue to clean up the issue queue. Feel free to reopen the issue if there is new information and the problem still resides. If not please make sure you close your issues that you don't need any more.
Maybe you can get support from the local user group. Please take a look at this list at groups.drupal.org.