NAME DBIx::JoinedColumnsAggregator - aggregating joined values SYNOPSYS use DBIx::JoinedColumnsAggregator; # using DBI use DBI; my $dbh = DBI->connect( 'dbi:SQLite:your_data', '', '' ); my $sth = $dbh->prepare( $a_sql_having_lef_join_closures ); $sth->execute(); my @data = aggregate_joined_columns( $sth, { pk => ['id'], tags => { product_codes => ['product_code'], groups => ['group_id', 'group_name'], }, access_style => 'hash', next_method => 'fetchrow_hashref', } ); for my $data ( @data ) { # $data->{ product_codes } return a list ref of 'product_code' # $data->{ groups } return a list ref of hash ref # having 'group_id' and 'group_name'. } # using ORM like DBIx::Skinny or Teng or etc. # use and setup the module... # ... my $itr = $skinny->search_by_sql( $a_sql_having_lef_join_closures ); my $data = $aggregate_joined_columns( $itr, { pk => ['id'], tags => { product_codes => ['product_code'], groups => ['group_id'], }, setter => sub { my ( $object, $tag, $rows ) = @_; $object->{ extra }->{ $tag } = $rows; }, } ); for my $data ( @{$data} ) { # $data->{ extra }->{ product_codes } return a list ref of 'product_code' # $data->{ extra }->{ groups } return a list ref of hash ref # having 'group_id' and 'group_name'. } DESCRIPTION This module provides a function which aggregates joined columns in using DBI or DBIx::Skinny or Teng and so on. You tell "pk" (primary key) and "tags" to the function. "pk" is used to determine representative objects. "tags" is used to determine target columns and a key name for accessing the aggregated data. FLLOW * Takes an iterator and iterates to get data objects. * Makes each first objects with primary key value representative objects. * Aggregates target columns exception of duplicated value. * Sets aggregated data into the representative objects. * Returns a list referance of the representative objects. NOTE: Duplicated value are ignored. So if you want to aggregate them, add other unique constrained column (ex. foreign key) into "tags" option. FUNCTION aggregate_joined_columns $list = aggregate_joined_columns( $iterator, $options ); @list = aggregate_joined_columns( $iterator, $options ); $list = aggregate_joined_columns( $arrayref, $options ); @list = aggregate_joined_columns( $arrayref, $options ); Takes an iterator or a list reference and returns a list reference (scalar context) or a list (list context). # some schema CREATE TABLE recipes ( id integer, name text, PRIMARY KEY ( id ) ); CREATE TABLE recipe_product_code_rels ( recipe_id integer, product_code varchar(10), PRIMARY KEY ( recipe_id, product_code ), FOREIGN KEY ( recipe_id ) REFERENCES recipes (id) ON DELETE CASCADE ); CREATE TABLE groups ( id integer, name text, PRIMARY KEY ( id ) ); CREATE TABLE recipe_group_rels ( recipe_id integer, group_id integer, PRIMARY KEY ( recipe_id, group_id ), FOREIGN KEY ( recipe_id ) REFERENCES recipes (id) ON DELETE CASCADE FOREIGN KEY ( group_id ) REFERENCES groups (id) ON DELETE CASCADE ); # your program my $dbh = DBI->connect( ... ); my $sth = $dbh->prepare(q{ SELECT r.id, r.name, code_rel.product_code, g.id AS group_id, g.name AS group_name FROM recipes r LEFT JOIN recipe_product_code_rels code_rel ON r.id = code_rel.recipe_id LEFT JOIN recipe_group_rels group_rel ON r.id = group_rel.recipe_id LEFT JOIN groups g ON g.id = group_rel.group_id }); $sth->execute(); my $recipes = $aggregate_joined_columns( $sth, { pk => ['id'], tags => { product_codes => ['product_code'], groups => ['group_id', 'group_name'], }, access_style => 'hash', next_method => 'fetchrow_hashref', } ); for my $recipe ( @$recipes ) { ... } # $recipe->{ product_codes } # => [ 'code1', 'code2', ... ] # # $recipe->{ groups } # => [ # { group_id => 1, group_name => 'group A' }, # { group_id => 2, group_name => 'group B' }, # { group_id => 3, group_name => 'group C' }, # ... # ] # In the case of single column in a tag, set a list reference of simple values. Otherwise a list reference of hash references. If no data, set an empty list reference. options pk Primary key(s) for foreign tables. pk => [ 'id' ] if there is a single primary key, the below code is acceptable too. pk => 'id' tags A hash reference of tags for grouping and columns . tags => { $group_tag1 => [ @tag1_columns ], $group_tag2 => [ @tag2_columns ], } Can use array references to set column alias. tags => { 'books' => [ ['book_id' => 'id'], ['books_title' => 'title'], ... ], ... } ... $data->{'books'}->[0]->{'id'}; # get column value instead of 'book_id' Note: single column with an alias in a tag is ignored. tags => { 'books' => [ ['book_id' => 'id'] ], ... } ... $data->{'books'}->[0]; # get value directly access_style Getting objects values style. 'hash' or 'method' or a code reference are acceptable. 'method' by default. You can set a subroutin reference to access data flexibly. access_style => sub { my ( $object, $column_name ) = @_; return $object->get_column( $column_name ); } So 'hash' is equivalent to: access_style => sub { my ( $object, $column_name ) = @_; return $object->{ $column_name }; } And 'method' equivalent to: access_style => sub { my ( $object, $column_name ) = @_; return $object->$column_name(); } setter A subroutine reference that sets aggregated values to objects. Four arguments are passed into it. First is an object expected to have aggreaged values. Second is a group tag set by "tags" option. Third is an aggregated values. Last is an option hash reference passed to aggregate_joined_columns. setter => sub { my ( $object, $tag, $rows, $options ) = @_; ... } Default setter: sub { my ( $object, $tag, $rows ) = @_; $object->{ $tag } = $rows; } next_method Calling this method against the iterator in aggregate_joined_columns. "next" by default. If you pass a list reference into aggregate_joined_columns instead of an iterator, you should not set "netx_method". Because the list reference is wrapped by a dummy iterator class using "next" method in that case. always_hash Single target column are aggregated as scalar value. If you set the option with true value, aggregated as hash reference. tags => { books => [ 'book_id' ], } # => $object->{ books } : [ 100, 123, .... ] always_hash => 1, tags => { books => [ 'book_id' ], } # => $object->{ books } : [ { book_id => 100 }, { book_id => 123}, .... ] EXPORT "aggregate_joined_columns" is exported by default. SEE ALSO DBI, DBIx::Skinny, Teng AUTHOR Makamaka Hannyaharamitu, <makamaka[at]cpan.org> COPYRIGHT AND LICENSE Copyright 2011 by Makamaka Hannyaharamitu This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.