[Dbix-class] Join question

[Kenneth S Mclane]

I am trying to convert my database views into actual queries. 
My view code:

sub list :Local {
        my ($self, $c, $page) = @_;
        $page = $c->req->param('page') || 1;
        my $rs = $c->model('ORANGES::Contacts')->search_rs(undef, { 
                order_by => 'contact_name',
                rows => 15,
                page => $page,
         });
        $c->stash(contacts => $rs);
        $c->stash(pager => $rs->pager());
        $c->stash->{'template'}=>'contacts/list';
}

This results in a paged result set of 60 pages of 15 record each. 
Resultant sql:

SELECT contact_id, contact_name, contact_email, contact_role, team_flag, 
department_id, department_code FROM (
  SELECT contact_id, contact_name, contact_email, contact_role, team_flag, 
department_id, department_code, ROW_NUMBER() OVER(  ORDER BY contact_name 
) AS rno__row__index FROM (
    SELECT me.contact_id, me.contact_name, me.contact_email, 
me.contact_role, me.team_flag, me.department_id, me.department_code  FROM 
HCDB_TEST.CONTACTS me
  ) me
) me WHERE rno__row__index >= ? AND rno__row__index <= ?

: '1', '15'
SELECT COUNT( * ) FROM HCDB_TEST.CONTACTS me:

My revised code, it's almost working:

sub list1 :Local {
        my ($self, $c, $page) = @_;
        $page = $c->req->param('page') || 1;
        my $rs = $c->model('ORANGES::Contact')->search_rs(undef, {
                join => 'department_id',
                order_by => 'contact_name',
                prefetch => 'department_id',
                rows => 15,
                page => $page,
        });
        $c->stash(contacts => $rs);
        $c->stash(pager => $rs->pager());
        $c->stash->{'template'}=>'contacts/list';
}

This results in only 5 records and I am not sure why. Resultant SQL:

SELECT contact_id, contact_name, contact_email, contact_role, team_flag, 
department_id, department_id__department_id, 
department_id__department_code, department_id__department_pid, 
department_id__manager_id FROM (
  SELECT contact_id, contact_name, contact_email, contact_role, team_flag, 
department_id, department_id__department_id, 
department_id__department_code, department_id__department_pid, 
department_id__manager_id, ROW_NUMBER() OVER(  ORDER BY contact_name ) AS 
rno__row__index FROM (
    SELECT me.contact_id, me.contact_name, me.contact_email, 
me.contact_role, me.team_flag, me.department_id, 
department_id.department_id AS department_id__department_id, 
department_id.department_code AS department_id__department_code, 
department_id.department_pid AS department_id__department_pid, 
department_id.manager_id AS department_id__manager_id  FROM 
HCDB_TEST.CONTACT me  JOIN HCDB_TEST.DEPARTMENT department_id ON 
department_id.department_id = me.department_id
  ) me
) me WHERE rno__row__index >= ? AND rno__row__index <= ?

: '1', '15'
SELECT COUNT( * ) FROM HCDB_TEST.CONTACT me  JOIN HCDB_TEST.DEPARTMENT 
department_id ON department_id.department_id = me.department_id: 

It seems that my code is doing funny things with the join, I had to modify 
my template code to get it to display the department_code by changing it 
from [% contact.department_code %] to [% 
contact.department_id.department_code %].

Some help with where I went wrong would be appreciated.

Regards

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120503/8af3d234/attachment.htm