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

First, try to stick with Cake's conventions. The class is GroupsMember
so the table should be groups_members. The columns should be member_id
and group_id.

Also, in a join table, you don't set the columns which are foreign
keys to be AUTO_INCREMENT. The columns they point to, in the groups
and members tables, are AUTO_INCREMENT so that the id is generated
automatically. What you want in the join table is the actual value
generated in the source tables. So "member_id int(11) NOT NULL" is
sufficient.

And in a join table, you can have a separate id column that is
incremented but it's usually unnecessary. Unless you have a good
reason for it you may as well remove that column. And instead of
setting a two-column PK you can make that a UNIQUE key instead.

Finally, you generally don't need to create a class for the join table
as you can use Cake's built-in methods from within the Member or Group
classes:

$this->GroupMember->find(...)


On Mon, Dec 31, 2012 at 8:32 AM, <pyroka@googlemail.com> wrote:
> 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.
>
>

--
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.