MySQL group concat a join select
This is an example of how you can aggregate data using a JOIN in a SELECT statement.
<?php
SELECT node.*, GROUP_CONCAT(example.nid) AS myfield FROM node LEFT JOIN example USING(nid) GROUP BY node.nid;
Result:
..."123, 65, 14, 65"...
?>Make the list semicolon separated and order the values descending and make sure that the values are distinct (no duplicates)
<?php
...GROUP_CONCAT(DISTINCT example.nid ORDER BY example.xyz DESC SEPARATOR '; ')...
?>
Result:
..."14; 65; 123"...If you want some formatting in your group concatenation.
<?php
..GROUP_CONCAT( CONCAT( "tid:", term_node.nid, "x" ) SEPARATOR "; " ) AS term_list ..
?>
Result:
..."tid:100x; tid:123x; tid:453x"...Or you can create other ready to use strings directly in your SQL statement like:
Result:
..."Price:100 USD; Price:123 USD; Price:453 USD"...
or
..."arg1/arg2/arg3"...Note that there is a limitation in how many characters a group concat can return and if you exceed that limit the result will be truncated and therefore your function will return a corrupt result.
The maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:
SET [GLOBAL | SESSION] group_concat_max_len = val;in Drupal I increase the value like this before my other db query with group_concat() in.
<?php
//Increase group concat limit
db_query("SET SESSION group_concat_max_len = 3072;");
?>
Permitted Values
Platform Bit Size 32
Type numeric
Default 1024
Range 4 .. 4294967295
Platform Bit Size 64
Type numeric
Default 1024
Range 4 .. 18446744073709547520