Idiomatic way of getting from database result set to HTML::Template data structure?

[davehodg]

I have a flat result set from a database, joining across three tables, for the sake of this example, call them magazines, issues and articles. Each magazine has a name, each issue a number and each article a name. The result set comes back something like:

$result = [ 
  { mag_name => "mag1", issue_num => "01", article => "art1" },
  { mag_name => "mag1", issue_num => "01", article=> "art2" },
  { mag_name => "mag1", issue_num => "02", article=> "art1" },
  { mag_name => "mag1", issue_num => "02", article=> "art2" },
  { mag_name => "mag3", issue_num => "01", article=> "art1" }
] ;

Straightforward stuff so far. Now, that structure needs to be mangled into something totally different for the benefit of HTML::Template whilst maintaining the order of things:

$data = [
  {
     'magazine_name' => 'mag1',
     'issue_loop' => [
       {
         issue_number => '01',
         article_loop => [
            { article_name => 'art1' }
            { article_name => 'art2' }
            ],
       },
       {
         issue_number => '02',
         article_loop => [
           { article_name => 'art1' }
           { article_name => 'art2' }
           ]
        },
        {
         'mag_name' => 'mag2',
         'issue_loop' => [
            {
            issue_number => '01',
            article_loop => [
              { article_name => 'art1' },
              { article_name => 'art2' }
             ]
             },
             {
               issue_number => '02',
               article_loop => [
                  { article_name => 'art1' },
                  { article_name => 'art2' }
              ]
          }
       ] ;

I've probably mislaid some parens there but hey.

So what's a nice way of transmogrifying the former into the latter whilst preserving the order of things?

TIA,

[dragonchild]

sub inflate
{
    my ($results) = @_;

    my %data;

    foreach my $results (@$results)
    {
        my ($mag, $issue, $article) = @{$results}{qw( mag_name issue_n
+um article )};
        push @{$data{$mag}{$issue}}, $article;
    }

    foreach my $mag (keys %data)
    {
        $data{$mag}{article_loop} = map {
            issue_number => $_,
            article_loop => $data{$mag}{$_},
        } keys %{$data{$mag}};
    }

    return \%data;
}

It doesn't preserve the order the issues for each magazine came back from the database, but sorting should be a view question, not a data retrieval question.

[Anonymous Monk]

If I could use the Template::Toolkit, the whole thing would most certainly have been a view problem, and indeed a no-brainer.

HTML::Template is a lot more constrained in that regard, coping only with variables, conditionals and iterators.

Ta,

[dragonchild]

You're missing the point. Sorting may be easier with TT, but H::T just prints out what you give it. Sort it before you hand it off to the View section. In other words, inflate your data, then sort it, then hand it off.

[matija]

Something like (untested):

my ($curmag,$curiss,$curart)=();
my (%mag,%iss,%art);
while (my $d=fetchrow_hashref) {

  if (($curiss eq $$d{issue_number) &&
      ($curmag eq $$d{mag_name)) {
     push(@iss,{article_name=>$$d{article_name}});

  } else { # new issue and/or magazine
    $mag{issue_loop}=\@iss if (defined($curiss));
    @iss=[{article_name=>$$d{article_name}}]; # not sure if that assig
+ns a new ref. to array. Check FIRST!
    $curiss=$$d{issue_number}
  }
  if ($curmag ne $$d{mag_name}) {
     push(@data,\%mag) if defined($curmag);
     %mag={}; # see if that assigns new ref, as above
     $mag{mag_name}=$$d{mag_name};
     push(@data,\%mag) if defined($curmag);
     $curmag=$$d{mag_name};
  }
}

Issues that I'd have to do more testing with: You need to assign a NEW array, and a NEW hash in the code. Possibly I'd have to work with references from the start instead of having a @iss and %mag.

You'd want to do something so the last line closes up the whole thing (perhaps a "fake" row with "undef" elements).

Hope this helps...

[Anonymous Monk]

That was where I was heading, keeping counts for magazine, issue and article, then resetting the inferior variables when the superior one ticked up. Still fairly 'orrid though!

[iguanodon]

I've found this pretty helpful.

[davehodg]

Thanks folks. I ended up with a nasty, yet straightforward state machine where we build a list of articles, then when we detect that we've ticked over to a new issue, push those onto the list of issues and then the same for magazines. Nasty, but it works!