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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
