Reading complex result-sets in Catalyst/Template

[kiz]

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,
[phone]}}","{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?

[thundergnat]

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 -%]