How can I use a different id field for one of my CakePHP model associations?

Question

I have an one to many association in which a Thing can have many Statuses defined as below:

Status Model:

class Status extends AppModel
{
    var $name = 'Status';

    var $belongsTo = array(
        'Thing' => array(
            'className' => 'Thing',
            'foreignKey' => 'thing_id',
    );
}

Thing Model:

class Thing extends AppModel
{
    var $name = 'Thing';    

    var $belongsTo = array(
        // other associations
    );

    var $hasMany = array(
        'Status' => array(
            'className' => 'Status',
            'foreignKey' => 'thing_id',
            'dependent' => false,
            'order' => 'datetime DESC',
            'limit' => '10',
        ),
        // other associations
    );
}

This works OK, but I would like Thing to use a different id to connect to Status. E.g. Thing would use 'id' for all of it's other associations but use 'thing_status_id' for it's Status association.

How can I best do this?

Answer 1

You can define the foreignkey whatever you want.So I think you can just change 'foreignKey' => 'thing_id', to 'foreignKey' => 'thing_status_id', and alter the column thing_id in the table of Statuses.

Answer 2

The status table I don't have much control over as it gets it data from another source (which uses its own Thing id's).

Maybe it's better to introduce a "translation table" and make a HABTM relationship out of it. It also counteracts the "pollution" of your Thing table with data that's not strictly necessary.

things
   id
   ...

   HABTM Status
       with => ThingAltId

thing_alt_ids
   id
   thing_id
   status_thing_id
   ...possibly other ids used to identify Thing in 3rd party systems...

statuses
   id
   thing_id

   HABTM Thing
       with => ThingAltId

Answer 3

'foreignKey' => false and 'conditions' => 'Thing.status_id = Status.thing_id' for association options can figure out what you asking. But I agree that using translation table for habtm relation.

Answer 4

In the end I could only get it to work by using a finderQuery with a SQL subquery.

Here is my Model:

class Thing extends AppModel
{
    // ...

    var $hasMany = array(
        'Status' => array(
            'className' => 'Status',
            'foreignKey' => 'thing_id',
            'dependent' => false,
            'limit' => 10,
            'finderQuery' => '
                SELECT Status.* 
                FROM statuses AS Status 
                WHERE Status.thing_id = (
                    SELECT Thing.status_thing_id 
                    FROM things AS Thing 
                    WHERE Thing.id = {$__cakeID__$}
                )
                ORDER BY Status.datetime DESC
                LIMIT 0, 10',
        ),
    ),

    // ...
}

The really weird thing is if I remove the 'limit' => 10 line the query no longer works properly, even though that line doesn't do anything (I still need the LIMIT 0,10 in my SQL).