Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Think about Loose Coupling
 
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
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 about the Monastery: (5)
As of 2014-04-19 00:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (473 votes), past polls