如何从一个或多个与模型类表没有直接关系的表中检索数据

Question

I'm forced to change my entire post, since I believe I was not able to make myself clear. Also, I got one answer but I believe it's due to my poor explanation of the problem at the first place that the answer is not very helpful in my case.

Allow me to explain more clearly. This below is my model:

class HomeLoanDistributionsDetail extends AppModel {

    var $name = 'HomeLoanDistributionsDetail';
    var $actsAs = array('Logable' => array(
            'userModel' => 'User',
            'userKey' => 'user_id',
            'change' => 'list', // options are 'list' or 'full'
            'description_ids' => TRUE // options are TRUE or FALSE
    ));
    var $validate = array(
        'entry_date' => array(
            'rule' => 'date',
            'message' => 'Enter a valid date',
            'allowEmpty' => true
        ),
        'branch_id' => array('numeric'),
        'customer_id' => array('numeric'),
        'loan_amount' => array('numeric'),
        'service_charge' => array('numeric'),
        'security' => array('numeric'),
        'loan_taken_term' => array('numeric'),
        'purpose_id' => array('numeric'),
        'installment_amount' => array('numeric'),
        'installment_service_charge' => array('numeric'),
    );
    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $belongsTo = array(
        'Customer' => array(
            'className' => 'Customer',
            'foreignKey' => 'customer_id',
            'fields' => 'id,name,fathers_or_husbands_name,credit_card_no,membership_type'
        ),
        'Branch' => array(
            'className' => 'Branch',
            'foreignKey' => 'branch_id',
            'fields' => 'id,name',
        ),
        'HomeLoanCategory' => array(
            'className' => 'HomeLoanCategory',
            'foreignKey' => 'category_id',
            'fields' => 'id,name',
        )
    );

I also have two other Model classes:

1. LoanGroup having database table loan_groups
2. LoanGroupsCustomer having database table loan_groups_customers

The most important points:

• LoanGroupsCustomer has a field named customer_id
• LoanGroupsCustomer has another field named loan_group_id, which is a foreign key of LoanGroup
• LoanGroup has a field named name. I want this field to be included in my HomeLoanDistributionsDetails model.

So basically, the corresponding SQL query that can get me the correct resultset is this:

SELECT D.name AS loan_group, 
CONCAT(B.name,'/',B.fathers_or_husbands_name) AS customer_name_father_husband_Name ,
B.credit_card_no,B.membership_type AS Member_Class,
A.entry_date AS loan_date,
A.loan_amount, 
A.service_charge,
A.security,
A.loan_taken_term,
E.name,
A.installment_amount,
A.installment_service_charge

FROM home_loan_distributions_details A

LEFT JOIN customers B ON A.customer_id = B.id
LEFT JOIN loan_groups_customers C ON B.id = C.customer_id
LEFT JOIN loan_groups D ON C.loan_group_id = D.id
LEFT JOIN home_loan_categories E ON A.category_id = E.id

WHERE A.customer_id = 18

GROUP BY A.id

I have absolutely no idea how I can do the following in my model class using the existing belongsTo association in my model class:

LEFT JOIN loan_groups_customers C ON B.id = C.customer_id
LEFT JOIN loan_groups D ON C.loan_group_id = D.id

Note that my home_loan_distributions_details table doesn't have any column like loan_group_id or loan_group that can be directly mapped to the loan_groups table as a foreign key. That's the main reason behind my problem.

I tried something like this in the belongsTo association part but got SQL error(naturally, as expected):

'LoanGroup' => array(
            'className' => 'LoanGroup',
            'foreignKey' => 'customer_id',
            'fields' => 'id,name'
        ),

CakePHP version - 1.2.5, PHP version - 5.2.11, MySQL version - 5.1.36.

Answer 1

You can run a sub-query, or you can load the model on the fly with ClassRegistry::init('region'); and call a find first, passing in the foreign key. I'd recommend the first though, as it'd be more efficient.