diff options
author | Mark Wells <mark@freeside.biz> | 2014-12-12 16:00:53 -0800 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2014-12-12 16:00:53 -0800 |
commit | c50ac2e99690064ac74868f076cc5590448d95aa (patch) | |
tree | 82be88d6ae260fddb75b8f86e36630fd9eba0995 | |
parent | d8230051d701b0e6105b7196a328dfaac980609b (diff) |
use the mysql equivalent of array_to_string, #32548
-rw-r--r-- | FS/FS/Record.pm | 21 | ||||
-rw-r--r-- | FS/FS/Report/Tax.pm | 8 |
2 files changed, 25 insertions, 4 deletions
diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 6e4f973df..462376691 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -38,7 +38,8 @@ use Tie::IxHash; #export dbdef for now... everything else expects to find it here @EXPORT_OK = qw( dbh fields hfields qsearch qsearchs dbdef jsearch - str2time_sql str2time_sql_closing regexp_sql not_regexp_sql concat_sql + str2time_sql str2time_sql_closing regexp_sql not_regexp_sql + concat_sql group_concat_sql midnight_sql ); @@ -3414,6 +3415,24 @@ sub concat_sql { } +=item group_concat_sql COLUMN, DELIMITER + +Returns an SQL expression to concatenate an aggregate column, using +GROUP_CONCAT() for mysql and array_to_string() and array_agg() for Pg. + +=cut + +sub group_concat_sql { + my ($col, $delim) = @_; + $delim = dbh->quote($delim); + if ( driver_name() =~ /^mysql/i ) { + # DISTINCT(foo) is valid as $col + return "GROUP_CONCAT($col SEPARATOR $delim)"; + } else { + return "array_to_string(array_agg($col), $delim)"; + } +} + =item midnight_sql DATE Returns an SQL expression to convert DATE (a unix timestamp) to midnight diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index 713be02df..f53be5d22 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -2,7 +2,7 @@ package FS::Report::Tax; use strict; use vars qw($DEBUG); -use FS::Record qw(dbh qsearch qsearchs); +use FS::Record qw(dbh qsearch qsearchs group_concat_sql); use Date::Format qw( time2str ); use Data::Dumper; @@ -109,7 +109,8 @@ sub report_internal { $select .= "NULL AS $_, "; } } - $select .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, "; + $select .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . + ' AS taxnums, '; $group =~ s/, $//; # SELECT/GROUP clauses for second-level (totals) queries @@ -120,7 +121,8 @@ sub report_internal { $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, "; $group_all = "GROUP BY $breakdown{pkgclass}"; } - $select_all .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, "; + $select_all .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . + ' AS taxnums, '; my $agentnum; if ( $opt{agentnum} and $opt{agentnum} =~ /^(\d+)$/ ) { |