building an HoAoH ... very badly

Cody Pendant has asked for the wisdom of the Perl Monks concerning the following question:

OK I know this code is hideous, believe me. That's why I'm seeking the wisdom! It's more of a logic problem than simply a Perl problem, but...

I'm getting some data out of SQL (fetchrow_hashref) in the form:

{season => 1, ep => 1, title => 'Hellmouth' },
{season => 1, ep => 2, title => 'Harvest'   },
{season => 1, ep => 3, title => 'Witch'     },
{season => 1, ep => 4, title => 'Teacher'   },
{season => 1, ep => 5, title => 'First Date'},
  # [etc]
{season => 2, ep => 13, title => 'Bad'      },
{season => 2, ep => 14, title => 'Assembly' },
{season => 2, ep => 15, title => 'School'   }
[download]

That is, it's a long list of episodes, some in season n, some in season n+1 and so on. I need to put it into a structure like this:

$appearances = 
[
    {season => 1, eps => 
      [ 
        {title => 'Hellmouth' },
        {title => 'Harvest'   },  
        {title => 'Witch'     },    
        {title => 'Teacher'   },  
        {title => 'First Date'}
      ]
    },
    
    {season => 2, eps => 
      [ 
        {title => 'Bad'       },
        {title => 'Assembly'  },
        {title => 'School'    },
      ]
    }
]
[download]

An array of hashes for each season, and each episode an entry in an array within that hash.

Here's how I did it late last night, and it works, but there must be a better way than this:

my $temp_hash   = {};
my $last_season = 0; 
my $appearances = [];
while ( my $ref = $sth->fetchrow_hashref() ) {
    if ( $ref->{season} != $last_season ) {
        # if the season has changed, unless
        unless ( $last_season == 0 ) {
            # unless it's the first season we encounter
            push( @{$appearances}, $temp_hash );
                # put the data for that season into 
                # the data structure
            $temp_hash = {};
               # make the hash empty again
        }
    }
    $temp_hash->{'season'} = $ref->{'season'};
     push( @{ $temp_hash->{'eps'} }, { title => $ref->{'title'} } );
    $last_season = $ref->{season};
    # put the season into the marker variable
}
push( @{$appearances}, $temp_hash );
    # need to put what's left over into the data 
    # structure after the while() finishes.
[download]

Where I build little structures and append them onto the big structure by watching for a change of season, with extra clauses for the first time around and so on.

I await your advice. Please be kind.

Update: Forgot to say, yes the data comes out in the right order, that's not part of the problem, and no, it's not an HoAoH, is it? It's an AoHoWhatever.

($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
=~y~b-v~a-z~s; print

Comment on building an HoAoH ... very badly Select or Download Code

Replies are listed 'Best First'.
Re: building an HoAoH ... very badly by Errto (Vicar) on Jan 10, 2005 at 03:53 UTC
Here's one way. I am assuming that within a given season the episodes are returned in ascending order. Since this is from a database query, if that isn't true now, just modify the SQL to make it true. First I build a HoAoH indexed by season number. `my %temp_hash; while (my $ref = $sth->fetchrow_hashref) { my $season = $ref->{season}; my $title = $ref->{title}; push @{$temp_hash{$season}}, { title => $title }; }` [download] Then I build it out. `my $appearances = [ map { +{ season => $_ , eps => $temp_hash{$_} } } sort keys %temp_hash ];` [download] Update: This solution has one small advantage in that it could work even if the seasons weren't numeric, but I kind of like broquaint's because it skips the intermediate step and also takes advantage of the seasons being ordered, which mine doesn't.	[reply] [d/l] [select]
Re: building an HoAoH ... very badly by Thilosophy (Curate) on Jan 10, 2005 at 04:04 UTC
If you you can fetch the data ordered (as your example suggests), you do not mind having extra keys in the episode hashrefs, and you know that your seasons run from first to n-th, you could fetch like this: `my $appearances = []; while ( my $ref = $sth->fetchrow_hashref() ) { my $season = $ref->{season}; unless ( exists $appearances->[$season -1 ] ) { $appearances->[$season -1 ] = { season => $season, eps => [] }; } my $eps = $appearances->[$season -1 ]->{eps}; push @$eps, $ref; }` [download] This should give you `$appearances = [ {season => 1, eps => [ {season => 1, ep => 1, title => 'Hellmouth' }, ......` [download] Disclaimer: untested Update: Note that since this code recycles the hashref from DBI, if you add more fields in your SQL later, they will show up in the final data structure as well.	[reply] [d/l] [select]
Re: building an HoAoH ... very badly by broquaint (Abbot) on Jan 10, 2005 at 05:09 UTC
Assuming well-formed data and sensible SQL then this should do the trick use Data::Dumper; ## thanks Ovid :) my @data = ( {season => 1, ep => 1, title => 'Hellmouth' }, {season => 1, ep => 2, title => 'Harvest' }, {season => 1, ep => 3, title => 'Witch' }, {season => 1, ep => 4, title => 'Teacher' }, {season => 1, ep => 5, title => 'First Date'}, {season => 2, ep => 13, title => 'Bad' }, {season => 2, ep => 14, title => 'Assembly' }, {season => 2, ep => 15, title => 'School' } ); my $appearances = []; for my $ref (@data) { my $s = \$appearances->[ $ref->{season} - 1 ]; $$s->{season} = $ref->{season}; push @{ $$s->{ eps } }, { title => $ref->{title} }; } print Dumper $appearances; [download] HTH `_________ broquaint`	[reply] [d/l]
Re: building an HoAoH ... very badly by Ovid (Cardinal) on Jan 10, 2005 at 04:08 UTC
I hope I didn't misunderstand what you were looking for. I wrote the following little test program with two assumptions: You're fetching the data in the order you've displayed. You either have all of the episodes or you do not care if you skip some so long as they are in order. Your title says "HoAoH", but the data structure you showed was different. use strict; use warnings; use Data::Dumper; $Data::Dumper::Indent = 1; my @data = ( {season => 1, ep => 1, title => 'Hellmouth' }, {season => 1, ep => 2, title => 'Harvest' }, {season => 1, ep => 3, title => 'Witch' }, {season => 1, ep => 4, title => 'Teacher' }, {season => 1, ep => 5, title => 'First Date'}, {season => 2, ep => 13, title => 'Bad' }, {season => 2, ep => 14, title => 'Assembly' }, {season => 2, ep => 15, title => 'School' } ); my @appearances; my $curr_season = 0; foreach my $data (@data) { my $record = { title => $data->{title} }; if ($data->{season} != $curr_season) { push @appearances => { season => $data->{season}, eps => [ $record ] }; $curr_season = $data->{season}; } else { push @{$appearances[-1]{eps}} => $record; } } print Dumper \@appearances; [download] Cheers, Ovid New address of my CGI Course.	[reply] [d/l]
Re: building an HoAoH ... very badly by Thilosophy (Curate) on Jan 10, 2005 at 05:04 UTC
This has nothing to do with your question, but while we are at it... You are fetching the whole result set into a memory structure. In this case, you could use DBI's `fetchall_arrayref` or even `selectall_arrayref` methods. The latter combines prepare, bind, and fetch into one call: `my $data = $conn->selectall_arrayref(q{ select season, episode, name from episodes where show = ? }, {Slice => {}}, 'Simpsons' );` [download] The `{Slice => {}}` gives you a hashref for every row (default is arrayref).	[reply] [d/l] [select]