cakephp binding model through another table

83 Views Asked by At

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.

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;
0

There are 0 best solutions below