CODEWITHSUNDEEP
mysqlcakephpcakephp-3.0
Hardik Patel
Hardik Patel

Reputation: 716

Connect with multiple database in CakePHP 3

I want to store some data histories and logs in other database table it may be very large database in terms of records in future. Also cross database SQL joing should be supported. So please help me for solution for this.

Thanks in advance

Upvotes: 2

Views: 2626

Answers (1)

Haresh Vidja
Haresh Vidja

Reputation: 8496

You have follow below steps fo use multiple datasource within same cakephp application.

Mention multiple database source in Config/app.php

You have to manage multiple data source configurations, in default data source keep main database, and make one more data source we can say it history for second data source. As mentioned below

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '<host name>',
        'username' => '<database user>',
        'password' => '<database password>',
        'database' => '<database name>',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],
    'history' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '<host name>',
        'username' => '<database user>',
        'password' => '<database password>',
        'database' => '<database name>',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ]
]

Specify data source in Tables Classes

In Src/Model/Table/<AnyOtherSource>Table.php, Add below method, where you want to use history data source. No need to add below method where you need to use default data source, for default data source CakePHP will take care of it.

public static function defaultConnectionName() {
    return 'history';
}

Joining and Model association in CakePHP 3

You access other databases on the same MySQL instance by prefixing the table with the appropriate database name. You have to specify table name with database_name.table_name, For this you need to add below code in each table classes.

For Example,

SELECT * FROM this_database.table_1 t1 JOIN that_database.table_2 t2 ON t2.column = t1.column

Add below code in Src/Model/Table/<All>Table.php

class LogsTable extends Table {

    public function initialize(array $config) {
        parent::initialize($config);

        $this->table($this->connection()->config()['database'] . "." . $this->table()); // this is very important for joining and associations.

        // your other code for initilize method
    }

    public static function defaultConnectionName() {
        return 'history';
    }

    // other methods and your code should be here

}

Upvotes: 7

Related Questions