comment on

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

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 &amp; 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}"}}"
[download]

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

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

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

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.

In reply to Reading complex result-sets in Catalyst/Template by kiz

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


laziness, impatience, and hubris
	PerlMonks