cakephp foreign key not the primary key

Question

I have a site developed in cakephp 2.0, I want to make a relation between two tables: activity_ingredients ``` 1 id int(10) UNSIGNED No None AUTO_INCREMENT 2 type_id tinyint(2) No None 3 activity_id int(11) No None 4 ingredient_id int(10) No None 5 created datetime ``` actions ``` 1 id int(10) UNSIGNED No None AUTO_INCREMENT 2 type_id tinyint(2) No None 3 language char(2) No None 4 text varchar(100) No None 5 created datetime ``` I want to associate the two tables with the field "type_id". I have done in this mode into my code: ``` class Action extends AppModel{ public $name = 'Action'; //non utilizzata nel sito è il nome del modello alla fine per migliorare la compatibilità public $belongsTo = array( 'ActivityIngredients' => array( 'className' => 'ActivityIngredients', 'conditions' => '', 'order' => '', 'foreignKey' => 'type_id' ) ); ``` } ``` class ActivityIngredients extends AppModel{ public $name = 'ActivityIngredients'; //non utilizzata nel sito è il nome del modello alla fine pe…

Accepted Answer

The first thing if you are using an "id" field in your "activity_ingredients" table, then you should use it as a foreignKey in another table.

A foreign key is a field in a relational table that matches a candidate key of another table.

Even if you are trying to use type_id as foreign key in "actions" table, Then type_id must be unique in your activity_ingredients table, and if it is so then you can define your ActivityIngredient Model as:

```
class ActivityIngredients extends AppModel{
    public $primaryKey = 'type_id';
    public $name = 'ActivityIngredients'; //non utilizzata nel sito è il nome del modello alla fine per migliorare la compatibilità

public $belongsTo = array(
        'Activity' => array(
            'className'     => 'Activity',
            'conditions'    => '',
            'order'         => '',
            'foreignKey'    => 'activity_id'
        ),
        'Ingredient' => array(
            'className'     => 'Ingredient',
            'conditions'    => '',
            'order'         => '',
            'foreignKey'    => 'ingredient_id'
        )
    );

public $hasMany = array(
        'Action' => array(
            'className' => 'Action',
            'conditions' => '',
            'dependent' => true,
            'foreignKey'   => 'type_id',
            'associatedKey'   => 'type_id'
        )
    );
}
```

And your Action Model will remain the same. And hence you will be able to fetch the desired records.

And even if you are not agree to define "type_id" as foreign key in your table. Then this code will work extremely well with your situation.

```
class ActivityIngredients extends AppModel{
public $name = 'ActivityIngredients'; //non utilizzata nel sito è il nome del modello alla fine per migliorare la compatibilità

public $belongsTo = array(
    'Activity' => array(
        'className'     => 'Activity',
        'conditions'    => '',
        'order'         => '',
        'foreignKey'    => 'activity_id'
    ),
    'Ingredient' => array(
        'className'     => 'Ingredient',
        'conditions'    => '',
        'order'         => '',
        'foreignKey'    => 'ingredient_id'
    )
);

public $hasMany = array(
    'Action' => array(
        'className' => 'Action',
        'conditions' => '',
        'dependent' => true,
        'foreignKey'   => false,
        'finderQuery'   => 'select * from actions as `Action` where
                            `Action`.`type_id` = {$__cakeID__$} '
    )
);
```

}

I am sure this will give you the desired result. Kindly ask if it not worked for you.

cakephp foreign key not the primary key

Solution

Problem