CakePHP 2 - Two foreign keys of a table linked to the same single table primary key

Question

How can i use cakephp 2X model hasone or other association concept here to execute the find query.

In my Schinfo.php model is

class Schinfo extends AppModel {
    public $tablePrefix = 'sko_';
    public $hasOne = [
        'State' => [
            'className' => 'Masterlocation',
            'foreignKey' => 'master_locid'
        ],
        'City' => [
            'className' => 'Masterlocation',
            'foreignKey' => 'master_locid'
        ],
        'Area' => [
            'className' => 'Masterlocation',
            'foreignKey' => 'master_locid'
        ]
    ];
}

With the above I got

SELECT
    `Schinfo`.`skool_id`,
    `Schinfo`.`skool_code`,
    `Schinfo`.`skool_name`,
    `Schinfo`.`skool_addr`,
    `Schinfo`.`master_state_id`,
    `Schinfo`.`master_city_id`,
    `Schinfo`.`master_area_id`,
    `Schinfo`.`skool_pin`,
    `Schinfo`.`skool_board`,
    `Schinfo`.`skool_type_id`,
    `Schinfo`.`skool_affilated_to`,
    `Schinfo`.`skool_affilated_no`,
    `Schinfo`.`skool_contact_no`,
    `Schinfo`.`skool_mailid`,
    `Schinfo`.`skool_website`,
    `Schinfo`.`skool_logo`,
    `Schinfo`.`skool_delete`,
    `State`.`master_locid`,
    `State`.`master_parentid`,
    `State`.`master_locname`,
    `State`.`is_checked`,
    `City`.`master_locid`,
    `City`.`master_parentid`,
    `City`.`master_locname`,
    `City`.`is_checked`,
    `Area`.`master_locid`,
    `Area`.`master_parentid`,
    `Area`.`master_locname`,
    `Area`.`is_checked` 
FROM
    `skoolata`.`sko_schinfos` AS `Schinfo` 
    LEFT JOIN
        `skoolata`.`sko_masterlocations` AS `State` 
        ON (`State`.`master_locid` = `Schinfo`.`id`) 
    LEFT JOIN
        `skoolata`.`sko_masterlocations` AS `City` 
        ON (`City`.`master_locid` = `Schinfo`.`id`) 
    LEFT JOIN
        `skoolata`.`sko_masterlocations` AS `Area` 
        ON (`Area`.`master_locid` = `Schinfo`.`id`) 
WHERE
    1 = 1

Now I need to change

LEFT JOIN
    skoolata.sko_masterlocations AS State
    ON (State.master_locid = Schinfo.id) 
LEFT JOIN
    skoolata.sko_masterlocations AS City 
    ON (City.master_locid = Schinfo.id) 
LEFT JOIN
    skoolata.sko_masterlocations AS Area 
    ON (Area.master_locid = Schinfo.id)

to

LEFT JOIN
    skoolata.sko_masterlocations AS State
    ON (State.master_locid = Schinfo.master_state_id) 
LEFT JOIN
    skoolata.sko_masterlocations AS City 
    ON (City.master_locid = Schinfo.master_city_id) 
LEFT JOIN
    skoolata.sko_masterlocations AS Area 
    ON (Area.master_locid = Schinfo.master_area_id)

to get my desire output

Answer 1

You want to define three relationships, Country, State and City and specify the className for each to be the model you are linking to, for example Location. You can then also specify the column you will be using as the foreign keys in your Student model using foreignKey:-

public $hasOne = [
    'Country' => [
        'className' => 'Location',
        'foreignKey' => 'country_id'
    ],
    'State' => [
        'className' => 'Location',
        'foreignKey' => 'state_id'
    ],
    'City' => [
        'className' => 'Location',
        'foreignKey' => 'city_id'
    ]
];

Then when it comes to finding the results you can use contain like:-

$students = $this->Student->find('all', [
    'contain' => ['Country', 'State', 'City']
]);