How to write a join query with "two database" in cakephp

Question

How to write a join query with two database in cakephp. Here is my code and I want to connect two db connection but I have a single Db Connection.

<?php
    namespace App\Controller;
    use App\Controller\AppController;
    use Cake\Datasource\ConnectionManager;
    use src\Model\Table\classScheduleDbConnTable;

    class TimeTableController extends AppController {
        public function index() {
            $jpDB = ConnectionManager::get('default');
            $ClsDB = ConnectionManager::get('test');
            $result = $ClsDB->execute('SELECT * FROM t_class_schedule')->fetchAll('assoc');
            $data=array();
            foreach ($result as $key => $value) {
                $data[$key]['id'] = $value['id'];
                $dates = date("Y-m-d", strtotime($value['classDate']));
                $data[$key]['fromTime'] = $value['fromTime'];
                $data[$key]['toTime'] = $value['toTime'];
                $data[$key]['keyWord'] = $value['keyWord'];
                $data[$key]['classDate'] = $dates;
            }
            $this->set('datas',$data);
        }
    }

You don't even use the CakePHP query builder, so that this seems more like a question related to whatever DBMS you are using, ie a plain SQL problem? **http://stackoverflow.com/questions/32033558/how-to-use-different-datasources-in-a-query-using-cakephp3** — ndm, Feb 23 '17 at 16:16

score 0 · Answer 1 · edited May 23 '17 at 11:46

I don't believe you can just "connect" two ConnectionManagers in this way.

You probably have two options:

Option A - Specify the database name statically as part of the Table's initialization per the example in this post:

public function initialize(array $config)
{
    $this->table('databaseName.tableName');
    // ...
}

Option B - Specify an alternative connection & Lazy Load

You can specify an alternative connection names for a specific Model at the Table level:

class ClassSchedule extends Table
{
    public static function defaultConnectionName() {
        return 'test';
    }
}

Note that this can't be used in a join on other Tables using the default connection. You'll instead have to "lazy load" associated data manually. As a general example (I don't know what your associations are, just a random example):

$student->class_schedules = TableRegistry::get('ClassSchedules')->find()
    ->where(['student_id'=>$student-id])
    ->toArray();

How to write a join query with "two database" in cakephp

1 Answers1