Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

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

by davehodg (Novice)
on Mar 29, 2004 at 14:18 UTC ( #340611=perlquestion: print w/ replies, xml ) Need Help??
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" } ] ;
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,

Comment on Idiomatic way of getting from database result set to HTML::Template data structure?
Select or Download Code
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}; } }
    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...

      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!
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; }

    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

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

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://340611]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (3)
As of 2014-08-31 02:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (294 votes), past polls