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

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" }
] ;

[download]
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' }
              ]
          }
       ] ;

[download]
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,

Replies are listed 'Best First'.
Re: Idiomatic way of getting from database result set to HTML::Template data structure?
by dragonchild (Archbishop) on Mar 29, 2004 at 15:14 UTC 
    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;
}

    [download]

    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.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

[reply]
[d/l]
      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,
[reply]
        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.

        ------
        We are the carpenters and bricklayers of the Information Age.

        Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

[reply]
Re: Idiomatic way of getting from database result set to HTML::Template data structure?
by matija (Priest) on Mar 29, 2004 at 15:13 UTC
    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};
  }
}

    [download]
    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...

[reply]
[d/l]
      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!
[reply]
Re: Idiomatic way of getting from database result set to HTML::Template data structure?
by iguanodon (Priest) on Mar 30, 2004 at 02:54 UTC
    I've found this pretty helpful.
[reply]
      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!
[reply]

Back to Seekers of Perl Wisdom