Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Reading complex result-sets in Catalyst/Template

by kiz (Monk)
on May 18, 2012 at 13:52 UTC ( #971304=perlquestion: print w/ replies, xml ) Need Help??
kiz has asked for the wisdom of the Perl Monks concerning the following question:

Environment: Perl 5.14; Catalyst 5.9; Template Toolkit 2.24; PostgreSQL 9.1

I have the following complex SQL query:

SELECT o.id , o.lat , o.long , o.city , o.country /* below used function array_to_array_agg() collects all attributes in +one array N.B. Attributes must be cast to varchar type beforehand because sql +arrays can't contain mixed data type */ , array_to_array_agg( ARRAY[[ n.id::varchar, --name.id n.name::varchar, --name of org n.acronym::varchar, --acronym of org n.npref::varchar, --is org's prefered name? n.pri::varchar, --is org's primary name? u.url::varchar, --an array of properties from urls table p.parent_id::varchar, --an array of parent_ids p.child_id::varchar --an array of child_ids ]] ) AS name_urls FROM org o -- link records in org table with matching records in name table LEFT JOIN name n ON (o.id = n.table_id) -- link records in org table with matching records in virtual table u +which is transformed version of urls table LEFT JOIN ( SELECT o.id , u.tbl , array_to_array_agg( ARRAY[[ u.id::varchar , u.url::varchar , u.pri::varchar , u.last_check_good::varchar , u.date_checked::varchar ]] ) AS url --aggregate url properties per org.id FROM urls u LEFT JOIN org o ON (o.id = u.table_id) GROUP BY o.id, u.tbl --force aggregation per org.id an +d tbl type ) u ON (o.id = u.id) -- link records in org table with matching records in virtual table p +which is a collection of parent and child ids per org.id LEFT JOIN ( SELECT o.id , array_accum(DISTINCT p.parent_id) AS parent_id --an array + of parent ids for org.id , array_accum(DISTINCT p.child_id) AS child_id --an arr +ay of child ids for org.id FROM org o LEFT JOIN parents p ON (o.id = p.child_id OR o.id = p.child_id) GROUP BY o.id --force aggregation per org.id ) p ON (o.id = p.id) WHERE u.tbl = 'org' AND n.tbl = 'org' AND o.id = 737 GROUP BY o.id, o.lat, o.long ORDER BY o.id;

that returns a nested data object...
(I use arrays of arrays to created the nested results)

If I do a query for a single org.id, I get the following return:

737;50.9;-1.4;"Southampton";"gb";"{{1373,"School of Electronics and Co +mputer Science",NULL,true,true,"{{1309,http://www.ecs.soton.ac.uk,tru +e,true,2012-03-27}}","{382}","{737}"},{716,"Electronics and Computer +Science",NULL,true,true,"{{1309,http://www.ecs.soton.ac.uk,true,true, +2012-03-27}}","{382}","{737}"},{70787,"Electronics & Computer Sci +ence",NULL,true,NULL,"{{1309,http://www.ecs.soton.ac.uk,true,true,201 +2-03-27}}","{382}","{737}"},{62106,"School of Electronics and Compute +r Science, University of Southampton",NULL,NULL,false,"{{1309,http:// +www.ecs.soton.ac.uk,true,true,2012-03-27}}","{382}","{737}"}}"

Using standard Perl, this is processed as a 6 element list, with $resultref->[5] being a reference to an anonymous array. In that sub-array, I can reference the elements, with the sixth element being a further array_ref... again, which can be looped through with a foreach loop.

I can further process the query response, and get something like this

Switching to Catalyst, I have made a virtual table (effectively a "view"):

use utf8; package ORI::Schema::Result::GetOrgs; use strict; use warnings; use parent qw(DBIx::Class::Core); __PACKAGE__->table_class("DBIx::Class::ResultSource::View"); __PACKAGE__->table("getorgs"); __PACKAGE__->add_columns( "id" => { data_type => "integer", is_auto_increment => 1, is_nullable => 0, sequence => "org_id_seq", }, "lat" => { data_type => "real", is_nullable => 1 }, "long" => { data_type => "real", is_nullable => 1 }, "city" => { data_type => "varchar", is_nullable => 1, size => 256 }, "country" => { data_type => "varchar", is_nullable => 1, size => 256 }, "name_urls" => # Actually an array of arrays { dat_type => "varchar[]", is_nullable => 1} ); # do not attempt to deploy() this view __PACKAGE__->result_source_instance->is_virtual(1); __PACKAGE__->result_source_instance->view_definition( &create_org_sele +ct() ); #### use a sub to return a string that is the SQL statement sub create_org_select { my $statement; $statement = "SELECT o.id , o.lat , o.long , o.city , o.country /* below used function array_to_array_agg() collects all attributes in +one array N.B. Attributes must be cast to varchar type beforehand because sql +arrays can't contain mixed data type */ , array_to_array_agg( ARRAY[[ n.id::varchar, --name.id n.name::varchar, --name of org n.acronym::varchar, --acronym of org n.npref::varchar, --is org's prefered name? n.pri::varchar, --is org's primary name? u.url::varchar, --an array of properties from urls table p.parent_id::varchar, --an array of parent_ids p.child_id::varchar --an array of child_ids ]] ) AS name_urls FROM org o -- link records in org table with matching records in name table LEFT JOIN name n ON (o.id = n.table_id) -- link records in org table with matching records in virtual table u +which is transformed version of urls table LEFT JOIN ( SELECT o.id , u.tbl , array_to_array_agg( ARRAY[[ u.id::varchar , u.url::varchar , u.pri::varchar , u.last_check_good::varchar , u.date_checked::varchar ]] ) AS url --aggregate url properties per org.id FROM urls u LEFT JOIN org o ON (o.id = u.table_id) GROUP BY o.id, u.tbl --force aggregation per org.id an +d tbl type ) u ON (o.id = u.id) -- link records in org table with matching records in virtual table p +which is a collection of parent and child ids per org.id LEFT JOIN ( SELECT o.id , array_accum(DISTINCT p.parent_id) AS parent_id --an array + of parent ids for org.id , array_accum(DISTINCT p.child_id) AS child_id --an arr +ay of child ids for org.id FROM org o LEFT JOIN parents p ON (o.id = p.child_id OR o.id = p.child_id) GROUP BY o.id --force aggregation per org.id ) p ON (o.id = p.id) WHERE u.tbl = 'org' AND n.tbl = 'org' GROUP BY o.id, o.lat, o.long ORDER BY o.id"; return $statement; }; 1;

If I use Catalysts AutoCRUD, I can see this table, so everything is working there

My problem is when I want to process the results with Template::Toolkit.

sub org : Local { my ( $self, $c ) = @_; $c->stash(types => [$c->model('ORIdatabase::GetOrgs')->all]); $c->stash( template => 'list/org.tt2' ); }

runs, and passes the data to:

[% USE Dumper -%] <table border='1'> <tr><th>City/Locale</th><th>Country code</th><th>latitude</th><th>long +ditude</th><th>Identity details</th></tr> [% FOREACH type IN types -%] <tr> <td><table style="border: solid red 1px">[% FOREACH nu IN type.nam +e_urls %] <tr> <td> [% nu.1 %] </td> <td> [% nu.2 %] </td> <td><ul> [% FOREACH id IN nu.5 %] <li style="border: solid navy 1px">[% Dumper.dump_html(id) % +]</li> [% END -%] </ul></td> </tr> [% END -%] </table></td> <td>[% type.city %]</td> <td>[% type.country %]</td> <td>[% type.lat %]</td> <td>[% type.long %]</td> </tr> [% END -%] </table>

The first level of array referencing works.... but I just cannot work out how to access the array I'm sure is held in nu.5!

I've been running around the DBIx::Class Cookbook, and the SQL::Abstract documentation too.... but I'm just not figuring it out....

How can I (can I?) reference this data?



-- Ian Stuart
A man depriving some poor village, somewhere, of a first-class idiot.

Comment on Reading complex result-sets in Catalyst/Template
Select or Download Code
Replies are listed 'Best First'.
Re: Reading complex result-sets in Catalyst/Template
by thundergnat (Deacon) on May 18, 2012 at 16:37 UTC

    Update: Clarified some ambiguities.

    I can't see any reason why that (template) code wouldn't work. It works for me locally (with some mocked up data in a structure as you described.) The headers are in the wrong position but you just need to shift them around a bit. See if this works for you locally. If this works for you, I suspect the data structure you are getting from your database query is not what you think it is in your program.

    Generates a file named test.html. Open it with a browser.

    use strict; use warnings; use Template; my $tt = Template->new(); my $data = [ { city => 'thiscity', country => 'thiscountry', lat => 'thislat', long => 'thislong', name_urls => [ [ '', 'info1', 'info2', '', '', ['url1','url2','ur +l3']], [ '', 'info3', 'info4', '', '', ['url5','url6','ur +l7']] ] }, { city => 'thatcity', country => 'thatcountry', lat => 'thatlat', long => 'thatlong', name_urls => [ [ '', 'info1', 'info2', '', '', ['url1','url2','ur +l3']], [ '', 'info3', 'info4', '', '', ['url5','url6','ur +l7']] ] }, ]; $tt->process(\*DATA, {types => $data}, 'test.html') or die $tt->error( +), "\n"; __DATA__ [% USE Dumper -%] <table border='1'> <tr><th>City/Locale</th><th>Country code</th><th>latitude</th><th>long +ditude</th><th>Identity details</th></tr> [% FOREACH type IN types -%] <tr> <td><table style="border: solid red 1px">[% FOREACH nu IN type.nam +e_urls %] <tr> <td> [% nu.1 %] </td> <td> [% nu.2 %] </td> <td><ul> [% FOREACH id IN nu.5 %] <li style="border: solid navy 1px">[% id %]</li> [% END -%] </ul></td> </tr> [% END -%] </table></td> <td>[% type.city %]</td> <td>[% type.country %]</td> <td>[% type.lat %]</td> <td>[% type.long %]</td> </tr> [% END -%]

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2015-07-30 23:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (273 votes), past polls