Paginate and JOINS

Dear All

I have a query about paginate and joins. I am developing with the latest V2 code.

I get a page of errors when I display a screen although my data is returned and displayed.

I am trying to display data from the registers table with some data from the associated heads and owners tables. The heads table is a sort of cornerstone table from which most of my other tables link. There is a one to many relationship between heads and registers. There is a one to one relationship between heads and owners.

I feel that the error is due to the pagination because when I click the links to sort, it doesnt seem to.

I have three tables defined as follows (I have left out stuff that is inapplicable):

CREATE TABLE heads (

id                      bigint(20) unsigned NOT NULL AUTO_INCREMENT,

ref    varchar(11),

hub    varchar(2), # owning hub

office    varchar(2), # owning office

created               DATETIME NOT NULL,

modified              DATETIME NOT NULL,

PRIMARY KEY (`id`)

) type=MyISAM;

CREATE TABLE registers (

id                      bigint(20) unsigned NOT NULL AUTO_INCREMENT,

head_id    int(10), # head foreign key

type    varchar(3),

description    varchar(55),

status    varchar(1),

created               DATETIME NOT NULL,

modified              DATETIME NOT NULL,

PRIMARY KEY (`id`)

) type=MyISAM;

CREATE TABLE owners (

id                      bigint(20) unsigned NOT NULL AUTO_INCREMENT,

head_id    int, # head foreign key

name_last    varchar(35),

name_first    varchar(55),

created               DATETIME NOT NULL,

modified              DATETIME NOT NULL,

PRIMARY KEY (`id`)

) type=MyISAM;

I have the following relationships between the tables (again I have left out stuff we are not interested in):

From the Heads controller

public $hasOne = array(

'Owner' => array(

'className' => 'Owner',

'foreignKey' => 'head_id'

)

);

public $hasMany = array(

'Register' => array(

'className' => 'Register',

'foreignKey' => 'head_id',

'dependent' => false

)

  );

  

From the Owners controller

  

  public $belongsTo = array(

'Head' => array(

'className' => 'Head',

'foreignKey' => 'head_id'

)

);

From the Registers controller

public $belongsTo = array(

'Head' => array(

'className' => 'Head',

'foreignKey' => 'head_id'

)

);

I use the following in my index procedure of the Registers controller:

$this->Register->recursive = 0;

$this->paginate = array(

      'fields' => array(

        'Owner.name_last',

        'Head.ref',

        'Register.*'

      ),

      'joins' => array(

        'type' => 'INNER JOIN',

        'table' => 'owners',

        'alias' => 'Owner',

        'conditions' => 'ON (Register.head_id = Owner.head_id)'

      )

    );

    $this->set('registers', $this->paginate());

    

The data is returned from the tables correctly. However, I get the following

error (a total of 8 times):

Notice (8): Undefined offset: 1 [CORE/Cake/Model/Datasource/DboSource.php, line 1671]

(the offset number goes from 1 to 4 twice).

It also shows me a snippet of code:

$count = count($query['joins');

for ($i = 0; $i < $count; $i++) {

    if (is_array($query['joins'][$i])) {

    

... and a context:

$query =  array(

'offset' => null,

'joins' => array(

),

'fields' => array(

),

'table' => '`cirrus`.`registers`',

'alias' => 'Register',

'limit' => (int) 20,

'conditions' => array(),

'order' => array(

),

'group' => null

)

$model = object(Register) {}

$count = (int) 5

$i = (int) 1

It is the $i that changes for each of the errors - 1 to 4 twice as mentioned above.

This is the SQL statment that gets generated:

SELECT `Owner`.`name_last`, `Head`.`ref`, `Register`.*, `Register`.`id`

FROM `cirrus`.`registers` AS `Register`

INNER JOIN owners Owner ON (Register.head_id = Owner.head_id)

LEFT JOIN `cirrus`.`heads` AS `Head` ON (`Register`.`head_id` = `Head`.`id`)

WHERE 1 = 1

ORDER BY `Register`.`in_date` desc

LIMIT 20

I am realy sorry that this is rather long but I felt I needed to give you the definitions

as well as the problem. I am really hoping that someone can sort me out and let me know

how to correct this error.

Many thanks in advance.

With regards

Graham

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php