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 and 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 array 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;
##</code><code>##

737;50.9;-1.4;"Southampton";"gb";"{{1373,"School of Electronics and Computer Science",NULL,true,true,"{{1309,http://www.ecs.soton.ac.uk,true,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 Science",NULL,true,NULL,"{{1309,http://www.ecs.soton.ac.uk,true,true,2012-03-27}}","{382}","{737}"},{62106,"School of Electronics and Computer Science, University of Southampton",NULL,NULL,false,"{{1309,http://www.ecs.soton.ac.uk,true,true,2012-03-27}}","{382}","{737}"}}"

##</code><code>##

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_select() );

#### 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 and 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 array 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;
##</code><code>##

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

##</code><code>##

[% USE Dumper -%]
<table border='1'>
<tr><th>City/Locale</th><th>Country code</th><th>latitude</th><th>longditude</th><th>Identity details</th></tr>
[% FOREACH type IN types -%]
  <tr>
    <td><table style="border: solid red 1px">[% FOREACH nu IN type.name_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>