NAME

DBIx::Class::Helper::ResultSet::CrossTab

DESCRIPTION

A helper to simulate crosstab functionality in DBIx::Class

REQUIRES

String::SQLColumnName

parent

METHODS

crosstab

     my $r = $s->search({}, { columns => [qw/fruit channel/], } )
               ->crosstab({}, { 
                                select  => [qw/fruit/], 
                                on => [qw/channel/], 
                                pivot => [ { sum => 'units' } ], 
                                group_by => [qw/fruit/] 
                              });

     $r->result_class('DBIx::Class::ResultClass::HashRefInflator');  # to inflate rows

ATTRIBUTES

pivot

This includes all functions and fields that must be summarized as an arrayref, in the same way as data can be aggregated in DBIx::Class.

these:

     pivot => [ { sum => 'units' } ]  # aggregate function and field
 
     pivot => [ \"sum(units)" ]       # literal SQL

will both provide a sum of units as the result of the crosstab, while this:

     pivot => [ \"sum(units)", { avg => 'units }, \"count(distinct country)"  ]  # mixed, multiple fields

will generate a total of units, the average and the number of distinct countries

on

This is the field (or function thereof) that will generate the column headers

     on => [qw/channel/]

will generate columns by channel

     on => [qw/channel country/]

will have one column per channel and country combination. It's not possible yet to pivot on a function of a field.

others

All other attributes work as documented in DBIx::Class::ResultSet

-head2 CHANGES

Version 0.006 adds functionality for nested summary functions and therefore breaks column naming.

COPYRIGHT AND LICENSE

Copyright 2016-2017 Simone Cesano

This library is free software; you may redistribute it and/or modify it under the same terms as Perl itself.

CREDITS

I owe the general idea to Giuseppe Maxia, and I re-used a couple of code snippets from Arthur Axel "fREW" Schmidt.

cpanm DBIx::Class::Helper::ResultSet::CrossTab

perl -MCPAN -e shell
install DBIx::Class::Helper::ResultSet::CrossTab