Re: Fixture for the model of a HABTM relationship generates invalid SQL

Hi Andrew,

I'm having exactly the same problem.. I have a HABTM relationship between (sports) matches and teams. When I try to create a fixture for the MatchesTeam class I get the same error:

Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'c0test.matches_teams' doesn't exist 

I have no idea how to repair this because all other fixtures work perfectly...

cricket: I'm sorry but you don't understand what the problem is. The author doesn't set the AUTO INCREMENT twice intentionally. CakePHP does this on its own. I have a single-column primary key (named id) and I still get this error.

I tried both importing the fixture data from my original table and generating them manually and no matter what I do the table (matches_teams) is not created.

Dne pondělí, 31. prosince 2012 14:32:15 UTC+1 pyr...@googlemail.com napsal(a):

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 unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.