/ 
DBIx-Class-Helper-ResultSet-CrossTab-0.007
River stage zero No dependents
/ DBIx::Class::Helper::ResultSet::CrossTab

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 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.