CakePHP 2 Composite Primary Key

Question

Lets say I have a table called positions where i save the coordinates of every computer, monitor or printer.

+-----------------+
| key | type | id |
+-----------------+
| 1   | PC   | 1  |
| 2   | PC   | 2  |
| 3   | MO   | 1  |
+-----------------+

In this scenario, type and id are the primary keys. I read Cake doesn't support composite primary keys and suggests to use direct querys for that. Is there really no workaround for that? Is it better to save the coordinates for PCs directly in the PC table or in a table especially for PC positions? That's hard to swallow.

Answer 1

Not supported until version 3

Composite primary keys aren't supported until CakePHP version 3.

You can make composite primary keys work, but it's not trivial to do so (in summary, treat/nominate one of the fields as the primaryKey and handle the other with callbacks; need to overwrite Model::exists, add conditions to any associations) - if it's possible to do so it's easier to add an unique key to the table - that will permit normal usage and all-round "it just works"-ness.

That's a polymorphic association

Know that what you've got in the question is a polymorphic association (Position belongsTo PC, Position belongsTo MO), while you could treat those two fields as the primary key they are really expressing the association - this behavior may be useful to you, it also contains examples of defining model associations using conditions which are possibly relevant to your use case.

For example based on the info in the question this schema will make usage easier:

CREATE TABLE `positions` ( 
  `id` int(11) unsigned NOT NULL auto_increment, // <- added
  `type` varchar(30) NOT NULL, 
  `foreign_id` int(11) unsigned NOT NULL, // <- called "id" in the question
  `key` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY  (`foreign_id`, `type`)
);

And this would be an example of the inverse model association:

class Pc extends AppModel { 

    public $hasOne = array( 
        'Position' => array( 
            'foreignKey' => 'foreign_id',
            'conditions' => array('Position.type' => 'PC'), 
            'dependent' => true 
        ) 
    ); 
}