add GROUP_CONCAT aggregate function; and move aggregate function helpers to a query extender?

Joachim,
Thanks for the patch; i'm trying to create a view with a one-to-many relation, the best solution to this (afaik) is to group-concat the multi values and explode them again when using them. I was actually surprised that only the basic min/max/avg/count aggregate functions were available; a group-concat seems a logical addition?
Will this patch be taken in the next release? (i'm new to drupal so i don't know how this gets done....)
Paul

http://stackoverflow.com/questions/970481/concat-all-column-values-in-sql this is very relevant. The array_to_string(ARRAY(SELECT unnest(array_agg(column)) ORDER BY 1), separator) should work on DB2, too.

Yes, i see the issue but as long as the idea remains the same, then a db abstraction could fill in the syntactical differences, right? And maybe also even to provide an explode-alike-function to unpack the values.
Paul

Query extenders can be written special for a certain db driver, just suffix with _mysql for example, sure that's not that important for this particular issue but a quite cool feature.

for postgresql you can use string_agg
http://www.postgresql.org/docs/9.0/static/functions-aggregate.html

or array_agg and array_to_string
http://www.postgresql.org/docs/8.4/static/functions-aggregate.html

Triggering the testbot.

Great work so far on the group_concat, has been very useful in a project I am currently working on.

Is it possible to add a settings form so we can specify a different separator. At the moment I have changed the code in the patch so that it uses a newline character which works for my case. I have had a look through the code and seen that there isn't any variable in the addAggregateGroupConcat function that could store this sort of setting.

Applying this patch manually I end up with : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax. Do I apply it against the wrong version (7.x-3.x-dev). What would I need to do to apply the patch in a regular way? I seem to be to stupid to deal with git. It would be great if anyone could help me.

#10: 1362524-3.views_.group-concat-aggregate.patch queued for re-testing.

First let me thank you for working on this patch. I think it is a very important feature to have. I am uncertain if this is the right place to post this problem/limitation, but I hope it's not too bad.

I am currently using the patch to display related nodes in a view (short explanation: I have "book" nodes and "author" nodes, connected through a relation (is written by) and I created a view of all books and for every book I list the title and all the authors).

I have noticed a problem with the patch when it comes to creating links to the original nodes.

Let's say book 1 that has two relations to author A and author B. It the views listing all the books, it lists "A, B" as the authors of book 1 as expected (well, actually it does not use a space after the comma, but whatever")

But: The problem is that the linking to the original content does not work when using GROUP_CONCAT. Instead of linking "A" to let's say node/10 and "B" to node/11, a link "A,B" is created which links to node/10,11. So, it's not possible to visit the page of the second author.

I am not too intimate with SQL, but I am not too surprised this happens, because it probably not only concatenates the values of the cells, but also the ids of the nodes and then drupal uses the concatenated node ids to create the link (at least that is how I imagine it), but I wonder if there is a way to make this patch support linking to the original content. This would make it possible to use the patch instead of using Views Field View, which seems pretty horrible form a performance perspective.

Is this available somewhere for views3 D6 ?

i have just used the patch in #10 successfully while working on my geocluster project. thanks!
didn't give it a throughout review, but it works and code looks clean.

Is this already committed on Drupal 7 version?

#10 applied and worked here with some small limitations:

• it can only be used once or the result is something crazy
• if you apply it to links (for example to linked titles of content) the link will finish being something like node/##,##,##
• Seems to have problems if the listed names includes special chars (á, é, í, etc.). For example one column that contains content types: "Iveas AUBWAUR" and "Xáoryki sw kabzanuwbri"
• Group concat DISTINCT is also needed

Isn't the link trouble listed here because the link processing is done after the values are fetched? Therefore, the lovely work done here (views integration is tricky stuff, and I have been looking for exactly this) does not have a method yet overriden for the formatting of the values. This patch makes the returned value (single value) be a string containing several commas and infromation. When the value is handed off to drupal, it is as a single string and Drupal uses the same formatting processors on this string that it does for others. Next version will need to override the link formatter when this is engaged.

Thanks a lot guys for all your comments and feedbacks.

It seems this issue is getting more activity now, after being paused for about two months, until #20 got back with a quick feedback summary.

Is this already committed on Drupal 7 version?

Unfortunately, it seems this is still going to need more work, testing, feedback, discussion, and I would think it would still need a bit more time before being committed.

joachim's comment (#22):

This works at the query level, and the formatter gets something like 'a, b, c'

reminded me of an idea I had initially to be able to add links or some kind of specific formatters.
Pretty much, I was thinking of some kind of mechanism to explode the concatenated list of values (for example, 'a, b, c'), with a specific separator, then format each value and concatenate them again for display.
But I quickly found out it could become much more complicated than what I thought, for example, for building the links, from values 'a, b, c'. If these were nids (or other objects IDs), then I guess it would already make things slightly easier to build the corresponding links, but otherwise, it could certainly become much more complicated. Not to mention variations on the separators for the values (before and after formatting) and such.....

I haven't done any tests of implementation of that yet, so I'm not even sure this would lead anywhere, but I just wanted to share this idea quickly to get some more feedback and discussions on this while there is some activity.

I also think items raised by #20 related with special characters and distinct should be addressed. In particular for the special characters, I didn't have time to take a closer look at this, but I would be very interested in hearing what others would have to say about it.

Any more feedback, comments, questions or testing would be greatly appreciated.
Thanks to all in advance.

I had a problem installing the patch, but I also wanted a way to add more aggregation functions, so I patched the original views_plugin_query_default::get_aggregation_info() to have a hook. So now you can add aggregation methods in any module by calling hook_aggregation_info. The second file attached is example code for adding a few new aggregate functions.

That's awesome dude, that will bring a lot of new custom options. Thanks, I can wait to see that committed and used.

I guess that is not a consistent approach, as the rest of views extends via plugins and handlers. In the given case you could simply extend views_plugin_query_default and override its get_aggregation_info() method.

The query handler should know what it is aware of, and should not be told that by other modules, as this is in the end only a matter of what the DB layer supports.

very solid and inspiring. BTW, this is something which i think the view is really lacking. Dashboard and any data analysis used a quite often of this feature.

Wondering if the patch that you posed is still the *best* way to go about this issue, as opposed to the way taken in #24. It seemed to me that #26 was suggesting that the #24 route was not a good route.

I am trying to build on this by adding some spatial aggregates for postGIS enabled Drupal installs.

Thanks,
r.b.

Testing notes on the text aggregate in PostgreSQL. There appears to be configuration issues with the settings specified in both of the patches supplied above, at least in my install of Drupal using postgreSQL. Also, because of the lack of the group_concat function in PG you have to roll your own to test the base case for this thread. I will present some code to set up the aggregates that are needed, and also I will share my modifications to the settings that I *think* are in error above (though it may just be my homegrown group_concat functions that are causing this mess).

I am not offering this as a patch because I am unsure if I should be patching the base code or the patch(es) given above. I appreciate any feedback.

Possible Errors in Config:
* Group_concat aggregates fail (creating an erroneous query) in Views on postgreSQL unless they are run as a "DISTINCT", which can be added with the following config option:

      'group_concat_distinct' => array(
        'title' => t('Group concat DISTINCT'),
        'extender' => 'ViewsAggregateQuery',
        'method' => 'addAggregateDistinct',
        'handler' => array(
          'argument' => 'views_handler_argument_group_by_numeric',
          'field' => 'views_handler_field',
          'filter' => 'views_handler_filter_group_by_numeric',
          'sort' => 'views_handler_sort_group_by_numeric',
        ),
      ),

* Using the group_concat aggregate on a text column in Drupal 7.x with the configs given creates a functioning piece of SQL that properly accesses the group_concat function, but when rendered by Drupal views it returns the string "0" instead of the concatenated string that the query correctly produces. This is fixed with the entry in the code given above where the views handler is specified:

          'field' => 'views_handler_field',

Code needed to use group_concat on postgreSQL.

create function concat_agg_accum(varchar, varchar) returns varchar
as 'select $1 || '', '' || $2'
language sql
strict immutable;

create function concat_agg_accum(integer, integer) returns varchar
as 'select $1 || '', '' || $2'
language sql
strict immutable;

create aggregate group_concat (
basetype = varchar,
stype = varchar,
sfunc = concat_agg_accum

There shouldn't be any space between the function name and the openning parenthesis. This patch fixes that for GROUP_CONCAT.

I applied the patch on the 3.x-dev batch the patch failed

Here's a re-roll of #30 for the March, 28 2014 update.

30: 1362524-30.views_.group-concat-aggregate.patch queued for re-testing.

try that again

Tried patch on 34 but says it's corrupt on line 25.

Cheers Dan

Reroll patch.

One is for use with the current 3.8 stable release and the other is for the current 7.x-3.x branch.

30: 1362524-30.views_.group-concat-aggregate.patch queued for re-testing.

I think a "Concatenation separator" option (or something like this) also would be useful, so the user could specify an optional separator value. Hardcoded concatenation methods may not be appropriate.

I've just joined to this queue. Patch #36 is flawed. It removes $info from function compile_fields(), and does not add 'method' => 'addAggregateGroupConcat' to array key 'group_concat', but adds 'method' => 'addAggregateSimple'. I eventually got it working. Nice :)

+1 for adding group_concat to views. It's very useful in shops.

Thanks @WillowDigit . Those steps worked for me too.
I created a patch for those fixes and attached it here along with interdiff between the patch in #36 and the new patch.

Patch from #42 worked for me fine.

Patch from #42 worked great for me too.

#42 works for me too, thanks!

Patch from #42 works great for me too.

+1 for add a settings form to specify different separators in follow up issue.

Patch worked fine with views merged rows + view rules for my specific requirement

Patch is looking good but I think we might want some tests to go with this.

This is necessary for #2578785: Large-scale location mapping in Drupal 8 (Views). Adding as a related issue. ALso wonder if this should generally be dealt with in a separate issue for D8 coverge.

For user's sake I've changed the GROUP_CONCAT separator to ', '. I've found no way to add arguments (for aggregation functions) to the admin page, so this will be like a temporary placeholder.

Manual fixing patch files is not good...

I think that this fits nicely in with the views plugin architecture. I have not looked through the D8 plumbing, but for D7, any number of aggregates may be exposed by modules by adding " + views_fetch_plugin_data('query_aggregate');" to the aggregate definition array. I have NOT updated the patch in this thread since it may wish to accomplish other things (like supporting the new extender class ViewsAggregateQuery). However, since the fix is so simple, I am copying the code below and creating a separate thread to make aggregate definitions pluggable.

  function get_aggregation_info() {
    // @todo -- need a way to get database specific and customized aggregation
    // functions into here.
    return array(
      'group' => array(
        'title' => t('Group results together'),
        'is aggregate' => FALSE,
      ),
...
    ) + views_fetch_plugin_data('query_aggregate');
  }

@robertwb: I guess you refer to issue: #2662548: Support Plugins for Views Aggregate. Will that be back-ported to D7?

Edit: OK, it got already back-ported: #1359298: Support Plugins for Views Aggregate in D7 (D8 Backport). What does this mean for this issue? Should GROUP_CONCAT now be implemented as a separate views plugin or is the patch in #42 or #52 still valid?

@osopolar - I think the patch will need to be reconfigured if using the latest dev from views. The approach used in this patch will still be valid, but I think it better to extend with the new framework for portability.

Still needs group_concat_distinct, here is my speedy chuck-it-in implementation:

  ...
+ 'group_concat_distinct' => array(
+   'title' => t('Group concat DISTINCT'),
+   'extender' => 'ViewsAggregateQuery',
+   'method' => 'addAggregateGroupConcatDistinct',
+   'handler' => array(
+     'argument' => 'views_handler_argument_group_by_numeric',
+     'filter' => 'views_handler_filter_group_by_numeric',
+     'sort' => 'views_handler_sort_group_by_numeric',
+   ),
+ ),
  ...
+ /**
+  * Add a GROUP_CONCAT(DISTINCT) expression to a query.
+  */
+ function addAggregateGroupConcatDistinct($group_type, $column_name, $fieldname, $placeholders) {
+   $separator = ', ';
+   $expression = "(GROUP_CONCAT(DISTINCT $column_name SEPARATOR '$separator'))";
+   $this->query->addExpression($expression, $fieldname, $placeholders);
+   return $this;
+ }
  ...

The attached patch adds in a group concact (Distinct) function, and also removes null entries from concats to prevent RESULT,,RESULT,,, from appearing (instead, it would appear as RESULT, RESULT)

Added the requested test.

Should the group_concat function also call on the CONVERT function, in order for it to work on non-Text datatypes? (see https://coderhub.wordpress.com/2012/10/17/apply-group_concat-and-concat-... for more background)

Will this be added on a release?

@lionguard - seems like a reasonable contingency, though to make it cross-db-platform it may require alternative function calls.

@bryanmanalo - not sure if group_concat has been added but it is now trivial to do so in a custom module.

@bryanmanalo: Currently waiting on people to review and test.

Minor whitespace cleanup.

I've been testing group concat and group concat distinct on nid fields and other entity type id fields with #58 applied for some time. (Not sure whether #59 and #64 are basically the same. Interdiffs would help...)

Two thing I've noticed:
- link to content is broken
- I've had some issues with standard count not working (anymore?), but unrelated to this patch I guess.

Didn't have time for investigation, yet.

@Eric_A As per my comments the only difference between #58, #59 and #64 is a test and some whitespace cleanup, neither touch the code functionality.

@MustangGB, great, thanks for the update. Wasn't 100% sure if your patch in #59 was based off of #58 by lionguard (which introduced the concat distinct and removing of null) or based off of an earlier patch without the concat distinct.

What's your stand on explicitly supporting or explicitly disabling formatters or formatting options like linking to content?

Seems like it might be out of scope of this issue, especially if the current aggregation methods don't support it either.

But then I'm currently using a custom solution for this sort of functionality (a context menu) so it doesn't effect me presently.

The less kittens killed the better? (lots of little patches > one mega patch)

Just dropping a note that this doesn't work on Postgres which doesn't have a GROUP_CONCAT function.

I have tested this with MySQL and worked for me without problems!

Patch in #64 works for me with the latest dev version.

Thanks

Now that #1359298: Support Plugins for Views Aggregate in D7 (D8 Backport) has been committed, GROUP_CONCAT can be achieved in a small custom module with the following code:

<?php

/**
 * Implements hook_views_plugins().
 */
function views_aggregation_group_concat_views_plugins() {
  return array(
    'query_aggregate' => array(
      'group_concat' => array(
        'title' => t('Group concat'),
        'method' => 'views_aggregation_group_concat_method_group_concat',
        'handler' => array(
          'argument' => 'views_handler_argument_group_by_numeric',
          'filter' => 'views_handler_filter_group_by_numeric',
          'sort' => 'views_handler_sort_group_by_numeric',
        ),
      ),
      'group_concat_distinct' => array(
        'title' => t('Group concat DISTINCT'),
        'method' => 'views_aggregation_group_concat_method_group_concat_distinct',
        'handler' => array(
          'argument' => 'views_handler_argument_group_by_numeric',
          'filter' => 'views_handler_filter_group_by_numeric',
          'sort' => 'views_handler_sort_group_by_numeric',
        ),
      ),
    ),
  );
}

/**
 * Callback function for the group_concat views aggregation group type.
 */
function views_aggregation_group_concat_method_group_concat($group_type, $field) {
  $separator = ', ';
  return "GROUP_CONCAT(NULLIF($field, '') SEPARATOR '$separator')";
}

/**
 * Callback function for the group_concat_distinct views aggregation group type.
 */
function views_aggregation_group_concat_method_group_concat_distinct($group_type, $field) {
  $separator = ', ';
  return "GROUP_CONCAT(DISTINCT NULLIF($field, '') SEPARATOR '$separator')";
}

Perhaps this patch is no longer necessary?

Bumping to the next release.

#72 works fine, thanks for this Chris!

Couple of notes though:

- each plugin definition in views_aggregation_group_concat_views_plugins() should have a file property defined as well, if you don't want to see any PHP notices,

- GROUP_CONCAT() is supported only by MySQL and family, so at least it should be wrapped in something like if (in_array(Database::getConnection()->databaseType(), array('mysql', 'mysqli', 'sqlite'))) {.... Even better if versions for other db engines are provided as well.

Removing this from the queue for the next release as I feel it needs a bit more consideration after #1359298: Support Plugins for Views Aggregate in D7 (D8 Backport) was committed (per #72).

Given the state of things I think a feature like this should be better worked on in core 8.x and then be backported.

#72 works great on 7.x-3.16. And I don't have to reapply as a patch on future updates. :)

Thanks drclaw!

When trying to use this with views data export, i get the error....

'Exception: SQLSTATE[HY000]: General error: 1260 Row 48 was cut by GROUP_CONCAT()'

I tried the patch for #64, but for my view, GROUP_CONCAT shows only the first item, not all the items concatenated. I have Views 7.x-3.8. Anyone else experience anything similar? It is accessing a field collection via a relationship.

I created the attached Drupal 7 module form @drclaw's code in #72 and implemented the suggestions from #74.

Edit:
I also created a sandbox project: https://www.drupal.org/sandbox/osopolar/views_aggregation_group_concat. If there are any issues, please post them there.

Thanks @osopolar for the module in #80. Should this feature request be closed as a "Won't fix" since #1359298: Support Plugins for Views Aggregate in D7 (D8 Backport) was committed (per #72)?

Maybe we have to wait for it to be fully supported module in repository and not just a sandbox project?

Re-roll of #64

The patch is great. However, I have problems trying to display concat dates.

public 'commerce_order_commerce_line_item_created' => string '1603027969, 1603025044' (length=22)

It's doing it, but in the view the field is empty. I think it's because of the format.

I have recreated the #83 patch with a modification in views/handlers/views_handler_field_date.inc in order to display multiple dates with concat. I had not test it for the rest of fields.

Sorry I made a mistake

Yes, this patch is great!

One gotcha comes when using the GROUP_CONCAT aggregation on a numeric column that contains zeroes. In this case, the zero values are excluded from the result. That's because NULLIF(0, '') returns NULL.

Since GROUP_CONCAT ultimately outputs a string anyway, should we stringify the inputs? i.e. in addAggregateGroupConcat and addAggregateGroupConcatDistinct, replace NULLIF($column_name, '') with NULLIF(CAST($column_name AS CHAR), '').

NULLIF('0', '') returns '0', so this way we retain the zeroes in the aggregated output.

#86 patch does not apply to latest version of views.

Rerolled the patch in #86 as requested in #88, thanks!