Associations not using foreign key

I have a model (AccountAgentDetail) that has 2 associations. One is a belongsTo (AccountUser) and the other is a hasOne(AccountProfile). The table for AccountAgent only has a FK relation to AccountUser. This model and the associated models are part of a plugin.

The issue I am seeing is that when the query is executed the join from AccountProfile to AccountAgentDetail is using the wrong association. It is using the id field of the AccountAgentDetail table instead of the fk field that I have defined in the AccountAgentDetail model.

This is the model that I am working with:

<?php
class AccountAgentDetail extends AccountModuleAppModel {
var $name = 'AccountAgentDetail';
var $primaryKey = 'agent_detail_id';

var $belongsTo = array(
    'AccountUser' => array(
        'className' => 'AccountModule.AccountUser',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
);

var $hasOne = array(
    'AccountProfile' => array(
        'className' => 'AccountModule.AccountProfile',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )   
);

public function getProspectiveAgents($count = 10)
{   
  开发者_StackOverflow中文版  return $this->find('all',
                 array(
                      'conditions'=>array('AccountAgentDetail.is_prospect'=>1),
                      'order'=>array('AccountAgentDetail.created_date DESC')
                 )
            );
    }
}
?>

This is the query that is executed when I call the method getProspectiveAgents. The issue I am seeing is in the second left join it is using AccountAgentDetail.agent_detail_id instead of AccountAgentDetail.user_id

SELECT
`AccountAgentDetail`.`agent_detail_id`,
`AccountAgentDetail`.`user_id`,
`AccountAgentDetail`.`is_prospect`,
`AccountAgentDetail`.`mls_id`,
`AccountAgentDetail`.`primary_office`,
`AccountAgentDetail`.`primary_board`,
`AccountAgentDetail`.`commission_plan`,
`AccountAgentDetail`.`referred_by`,
`AccountAgentDetail`.`referral_source`,
`AccountAgentDetail`.`previous_brokerage`,
`AccountAgentDetail`.`created_date`,
`AccountAgentDetail`.`last_modify_date`,
`AccountAgentDetail`.`created_by`,
`AccountAgentDetail`.`last_modifed_by`,
`AccountUser`.`user_id`,
`AccountUser`.`user_name`,
`AccountUser`.`user_pass`,
`AccountUser`.`user_status`,
`AccountUser`.`user_group`,
`AccountUser`.`instance_id`,
`AccountUser`.`is_logged_in`,
`AccountUser`.`is_visible`,
`AccountUser`.`created_by`,
`AccountUser`.`last_modified_by`,
`AccountUser`.`created_date`,
`AccountUser`.`last_modified_date`,
`AccountProfile`.`profile_id`,
`AccountProfile`.`user_id`,
`AccountProfile`.`first_name`,
`AccountProfile`.`middle_name`,
`AccountProfile`.`last_name`,
`AccountProfile`.`birth_date`,
`AccountProfile`.`ssn`,
`AccountProfile`.`employee_id`,
`AccountProfile`.`hire_date`,
`AccountProfile`.`sever_date`,
`AccountProfile`.`rehire_date`,
`AccountProfile`.`created_by`,
`AccountProfile`.`last_modified_by`,
`AccountProfile`.`created_date`,
`AccountProfile`.`last_modify_date`
FROM
`account_agent_details` AS `AccountAgentDetail`
LEFT JOIN `account_users` AS `AccountUser` ON(
`AccountAgentDetail`.`user_id` = `AccountUser`.`user_id`
)
LEFT JOIN `account_profiles` AS `AccountProfile` ON(
`AccountProfile`.`user_id` = `AccountAgentDetail`.`agent_detail_id`
)
WHERE
`AccountAgentDetail`.`is_prospect` = 1
ORDER BY
`AccountAgentDetail`.`created_date` DESC

You have decalared 'agent_detail_id' as primary key, it seems to me logical to select this as primary key for a join instead of user_id which is foreign key! Although I don't know exactly how this class works in the background I think you should work your model.

In your place I would work it in my mind out of the mvc-class-model context, thinking only under the E-Relational "frame".

Not sure about that...maybe you business requirements are such that you can share the primary key user_id, practically speaking having it both as foreign key and primary key in AccountAgentDetail if it is a ono-to-one.