If I have the field Title instead of Nid on the "Fields to Aggregate with the SQL function:" it just doesn't work.

With the field Title I get this SQL:

SELECT node.nid AS nid,
   casetracker_case.pid AS casetracker_case_pid,
   COUNT(node.title) AS node_title
 FROM node node 
 LEFT JOIN casetracker_case casetracker_case ON node.vid = casetracker_case.vid
 WHERE (node.type IN ('casetracker_basic_case')) AND (node.status <> 0)
 GROUP BY casetracker_case_pid, nid
  ORDER BY casetracker_case_pid ASC

With the field Nid I get this SQL (the correct one):

SELECT node.nid AS nid,
SELECT COUNT(node.nid) AS nid,
   casetracker_case.pid AS casetracker_case_pid
 FROM node node 
 LEFT JOIN casetracker_case casetracker_case ON node.vid = casetracker_case.vid
 WHERE (node.type IN ('casetracker_basic_case')) AND (node.status <> 0)
 GROUP BY casetracker_case_pid
  ORDER BY casetracker_case_pid ASC

Thanks.
inrofini

Comments

Mixologic’s picture

Mixologic
He/Him/His
Primary language English
Location Portland, OR
commented

I think this actually might be a bug in views. I could be wrong, but looking at views/includes/query.inc,

It appears as though using views_query and setting a group by will cause it to create group by clauses for *everything* you put in the query, whether you need it grouped on that field or not.

I took out the array_merge and just imploded the $this->groupby and suddenly I have a really useful powerful module..
Im sure Merlin has a reason for putting *everything* into the group by clause if there is a group by clause.

00955 if ($has_aggregate || $this->groupby) {
00956 $groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";
00957 if ($this->having) {
00958 $having = $this->condition_sql('having');
00959 }
00960 }

Mixologic’s picture

Mixologic
He/Him/His
Primary language English
Location Portland, OR
commented

Nevermind. I understand why Merlin is doing this now:
http://drupal.org/node/385158

neffets’s picture

neffets commented

see issues #385158: query->add_groupby & aggregate vs. nonaggregate fields (comment of merlin to the reason to patch views -> for ANSI SQL compliance
and issue #651546: Group By not working due to vid

to fix the problem:
a) $this->query->fields[fields]->aggregate='no' for all non group_by fields would break the views patch (see first issue)
but can be we can use the out-commented
unset this field IF it has not to be shown

or b if we are distinct)
$this->query->distinct=TRUE; $this->query->no_distinct='views_groupby'; //prevent views-behaviour to add nid(base_field) allways

PeterZ’s picture

PeterZ commented

While not pretty, I used the advice here:
http://drupal.org/node/409808

Steps:
1) I built the view.
2) Then displayed the SQL.
3) Removed the extraneous nid in the Group By clause,
4) Cut and pasted this new SQL query as an additional function into the views module as defined in the above post. By doing this, I override the views generated SQL that generates errors with a SQL statement that does work (no disrespect to the views module which is quite great).