[Catalyst] Troubles with mysql joins and template toolkit

[Mauro Andreolini]

Hi everyone,

I am using the Catalyst framework (version 5.69) to develop an academic
Web site (through an application called "science"). One of the pages
(URL: http://science:3000/teachers/last.first) is supposed to give
information about the teacher "last first". To this purpose, I have done
the following steps.

1. Creation of mysql database tables
I have created two tables: teacher and role. Teacher has logistic
information about a given teacher, role describes the role (Full or
Associate Professor, Researcher, and so on). I would like the two tables
to be joined in order to display the result:

select first,last,role.title from teacher inner join role role on
teacher.id_role = role.id where teacher.first = '?' and
teacher.last='?';


create table teacher (
  id bigint(20) unsigned not null auto_increment,
  first varchar(30) default NULL,
  last varchar(30) default NULL,
  role_id bigint(20) unsigned default NULL,
  key role_id_idx ( role_id ),
  constraint 'teacher_ibfk_1' foreign key ( 'role_id' ) references
role(id),
  primary key  (id),
) engine=innodb;

create table role (
  id bigint(20) unsigned not null auto_increment,
  title varchar(30) default NULL,
  code varchar(3) default NULL,
  primary key  (id),
) engine=innodb;

2. Creation of a data model
I have created models for those tables (and for all others, too) using
the DBIC::Plain model:

/path/to/science/science_create.pl model DBIC DBIC::Plain
dbi:mysql:science user pw

I have also created DBIC classes to build the necessary tables. Among
the others:

/path/to/science/lib/Model/DBIC/Teacher.pm:
package science::Model::DBIC::Teacher;
use base 'DBIx::Class::Core';

__PACKAGE__->table('teacher');
__PACKAGE__->add_columns( qw/id first last role_id/ );
__PACKAGE__->add_relationship(
  role => 'science::Model::DBIC::Role',
  { 'foreign.id' => 'self.role_id' }
);

The last statement is supposed to create a relation between the teacher
and its role, right?

3. I have created a Catalyst controller that tracks the
http://science:3000/teachers/last.first url.

/path/to/science/lib/science/Controller/Teacher:

...
sub single : LocalRegex( '(\w+).(\w+)') {
  my ($self, $c ) = @;

  # get first and last name
  my $last = $c->req->snippets->[ 0 ];
  my $first = $c->req->snippets->[ 1 ];

  # the query
  my @tch = $c->comp('DBIC')->class('Teacher')->search(
    {
      'last' => $last,
      'first' => $first
    },
    {
      include_columns => [ 'role.title' ],
      join => 'role'
    }
  $c->stash->{template} = 'docente';
  $c->stash->{teacher} = \@tch;}
}

...

sub end : Private {
  my ( $self, $c ) = @_;

  $c->forward ( $c->view('science::View::TToolkit') ) unless
$c->response->body;
}

4. I have created a TToolkit View for the page. The corresponding
template looks like this:

[% FOREACH t = c.stash.tch %]
  <h1>[% t.first %] [% t.last %]</h1>
  [% t.role %]
[% END %]

Now, as hard as I try, I cannot get the t.role variable to display :-(
Instead, I get an error screen telling me that 'role' is undefined.
This is quite logical, because, surfing through the Catalyst classes,
I see that t is a 'science::Model::DBIC::Teacher' blessed structure
(that reflects the teacher table), so I think it cannot contain fields
from another table (role). But how can I display the fields of an inner
join that do not belong to the teacher table?
Any suggestion is welcome.

Bye
-- 
Ing. Mauro Andreolini - Ph.D., Research Associate
Dipartimento di Ingegneria dell'Informazione
Università di Modena e Reggio Emilia - Italia
e-mail: andreolini.mauro at unimore.it
www: http://weblab.ing.unimo.it/people/andreolini/
tel: +39 059 2056256, fax: +39 059 2056129