How to use cross DB join in CakePHP where database have prefix

Question

When I use cross DB join in CakePHP having prefix for database tables in database.php I get error.

$joins[] = array('table' => 'candidates.ec_case_masters',
                    'alias' => 'CandidateCaseMaster',
                    'type' => 'INNER',
                    'conditions' => array(
                            'CandidateCaseMaster.candidate_id = Candidate.id',
                    )
            );

candidates database has prefix = ec_. so CakePHP adds ec_ in table name which becomes ec_candidates.ec_case_masters, but actually it should be candidates.ec_case_masters.

can some one help how can I use cross db join in CakePhp

Answer 1

You have to configure a 2nd data source and set the prefix for both and then use that db config for your model.

In your database config file:

• $default = array('prefix' => 'ec_', ....);
• $candidates = array('prefix' => 'candidates', ...);

Model:

public $useDbConfig = 'candidates';