I have 3 tables. software, permission and centre. I can make an association between software and permission and between centre and permission table. I want to get available softwares of specific centre. For example, when I access /centres/:id/softwares.json
, that will return me like the following format.
"Centre": {
"id": "1",
"name": "centre1",
"alias": "CN1"
"Software": [
{
"id": "1",
"name": "software1",
"description": "description1"
},
{
"id": "2",
"name": "software2",
"description": "description2"
},
...
]
}
This is my ER diagram.
Model Centre (need help here)
public $hasMany = 'Permission';
public $hasAndBelongsToMany = array(
'Software' => array(
'className' => 'Software',
'joinTable' => 'permissions',
'foreignKey' => 'id',
'associationForeignKey' => 'software_id',
'unique' => true,
)
);
That's the sample SQL which retrieves the available softwares for the specific centre (id: 1)
SELECT s.* FROM softwares s JOIN permissions p ON p.software_id = s.id WHERE p.centre_id=1;