Monday, December 31, 2012

Fixture for the model of a HABTM relationship generates invalid SQL

Hey,

I'm having a slight issue with a fixture I'm attempting to create.

Basically I have a Member model and a Group model, and they have a HABTM relationship, I've specified a custom model to handle this relationship 'GroupsMember' as I wish to add methods to that model to query the join table directly.

So when I set this table up in the databse (I use MySQL) I set it up like this:

CREATE TABLE IF NOT EXISTS `member_group` (
`member_id` int( 11 ) NOT NULL ,
`grp_id` int( 11 ) NOT NULL ,
PRIMARY KEY ( `member_id` , `grp_id` )
) ENGINE = InnoDB DEFAULT CHARSET = latin1;


This was all working ok until I attempted to create a fixture for the 'GroupsMember' model. The code I use to do that is as follows:

class GroupsMemberFixture extends CakeTestFixture 
{
public $useDbConfig = 'test';
public $table = 'member_group';

public $fields = array(
         'member_id' => array('type' => 'integer', 'null' => false, 'key' => 'primary'),
         'grp_id' => array('type' => 'integer', 'null' => false, 'key' => 'primary'),
      );

public $records = array(
array('member_id' => 0, 'grp_id' => 1),
);
}

However, when I run the test I get the following error:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'instrumentation_test.member_group' doesn't exist

So I decided to log all the SQL queries being generated, and the last one is this:

CREATE TABLE `instrumentation_test`.`member_group` (
`member_id` int(11) NOT NULL AUTO_INCREMENT,
`grp_id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY  (`grp_id`)) ENGINE=MEMORY;

If I run that in phpMyAdmin then it fails with the following error:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

So the addition of the AUTO_INCREMENT is messing things up, and the primary key is only set to be the grp_id not both the grp_id and the members_id.

Is there any-way I can override the SQL cake is generating for this case?

If I change it so that I create the table in the test database and tell the fixture to import the structure from there (using public $import = 'GroupsMember';) then cake generates the following SQL:

CREATE TABLE `instrumentation_test`.`member_group` (
`member_id` int(11) NOT NULL AUTO_INCREMENT,
`grp_id` int(11) NOT NULL AUTO_INCREMENT,
`id` varchar(255) NOT NULL, PRIMARY KEY  (`id`)) ENGINE=MEMORY;

which apart from adding the extra 'id' column has the same AUTO_INCREMENT issue.

What is it that tells cakephp that those fields should be AUTO_INCREMENT? And is it possible for me to make cake specify the primary key as the same as it was for the table I generated?

Thanks,
Andrew

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
 
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 

No comments: