This issue is raised with the following code snippet from views_handler_argument::summary_basics():

$field = "DISTINCT($field)";
...
$count_alias = $this->query->add_field(NULL, "COUNT($field)", 'num_records');

While this is a correct way to implement it, views_query::query() cannot respond to the field being set as DISTINCT or having an aggregate function. This is necessary in issue http://drupal.org/node/291079 where views_query::query() needs to add non-aggregate fields to the GROUP BY if an aggregate function has been used.

There are several ways to deal with this issue and i'm happy to roll a patch for the desired way. I need to know which way is desired. :)

  1. Change views_query::add_field($table, $field, $alias = '') to views_query::add_field($table, $field, $alias = '', $params = NULL) and have $params be an array('count' => TRUE/FALSE, 'distinct' => TRUE/FALSE).
  2. Add views_query::add_count($table, $field, $alias = '') and views_query::add_distinct($table, $field, $alias = '').
  3. Both of the above

--
Sammy Spets
Drupal core PostgreSQL maintainer

Synerger Pty Ltd
http://synerger.com

CommentFileSizeAuthor
#5 291292_count_distinct_fields_prop.patch2.27 KBjaydub
#1 291292_count_distinct_field_props.patch2.3 KBsammys

Comments

sammys’s picture

sammys commented
StatusFileSize
new 291292_count_distinct_field_props.patch2.3 KB

Here is a patch for 1) above.

sammys’s picture

sammys commented

Oh and credit to cwoodruf for this patch. I've modified cwoodruf's patch so add_field() can be used to modify the params.

merlinofchaos’s picture

merlinofchaos commented

Hmm. 1) above is interesting. I need to cogitate on it and see if I can think of anything it breaks.

sammys’s picture

sammys commented

*bump*

jaydub’s picture

jaydub commented
Status: Active » Needs review
StatusFileSize
new 291292_count_distinct_fields_prop.patch2.27 KB

I too ran into this after trying out the Views 2 documentation 'Getting Started' examples. I use PostgreSQL for 90% of my tinkering and try to help where I can. I've generated updated patches to this issue and to #291079: postgresql issue with group by clauses in argument processing . I certainly don't know enough of the Views 2 internals to call this a true patch review but I'll settle for submitting a re-roll in hopes that some progress can be made here.

After applying the 2 re-rolled patches I don't run into the problems with the GROUP BY in PostgreSQL. I exported the view to a MySQL based d6 install running on the same drupal codebase and the the view runs without error.

Tested on PostgreSQL 8.3

jaydub’s picture

jaydub commented
Version: 6.x-2.0-rc1 » 6.x-2.x-dev
merlinofchaos’s picture

merlinofchaos commented
Status: Needs review » Needs work

Applying the most recent version of this patch (thanks jaydub) results in this error when viewing the built in 'glossary' view:

user warning: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause query: SELECT SUBSTR(node.title, 1, 1) AS title_truncated, COUNT(DISTINCT(node.nid)) AS num_records FROM node node ORDER BY title_truncated ASC LIMIT 0, 36 in /var/www/views2/sites/all/modules/views/includes/view.inc on line 728.

merlinofchaos’s picture

merlinofchaos commented
Status: Needs work » Fixed

Ok, this is simply because a groupby command was inadvertantly removed in the patch. Restoring that makes things work and everything else looks ok, I think. COmmitted.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.