Using multiple databases in a query

Need Help
1

Hello, i have 2 databases and what i need to do is take the data from a table and copy it to the other. I have access to both and i already have the models too, but i don’t know how to make a query to insert db1 data into db2.
I appreciate any help!!! Also sorry for my english.

2

I don’t know the answer specifically for Cake, but if address 2 (or more) databases in a query, you have to prefix the tables with the db name, as in db1.table.column, db2.table.column, etc.

3

in app.php create another source in 'Datasources' block and then make use of it in getting table

use Cake\Datasource\ConnectionManager;
use Cake\ORM\TableRegistry;

...

// you can use `connectionName` instead also
$usersTable = TableRegistry::getTableLocator()->get('Users', [
            'connection' => ConnectionManager::get('secondary')
        ]);

there is also possibility to create another model class that will use this connection by overwriting defaultConnectionName method

4

This! Thank you very much, i read it a days ago in the documentation but i wasn’t sure and didn’t try it. It works just fine! I really aprecciate your time. Both of you. Have a great day.

5

Now i have another problem.
What i’m trying to do is this:

public function import()
{
$usersRiafo = TableRegistry::getTableLocator()->get(‘Users’, [
‘connection’ => ConnectionManager::get(‘riafo’)
]);
$select = $this->MdlUser->find(‘all’)
->select([‘id’, ‘username’, ‘firstname’, ‘lastname’, ‘email’, ‘phone1’, ‘address’, ‘city’, ‘country’]);
$query = $users->query()
->insert([‘id’, ‘dni’, ‘nombre’, ‘apellido’, ‘email’, ‘telefono’, ‘direccion’, ‘ciudad’, ‘pais’])
->values($select)
->execute();
return $this->redirect([‘action’ => ‘index’]);
}

but then it gave me an error:
Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘riafo.mdl_user’ doesn’t exist
I don’t understand why it try to access to riafo.mdl_user. It should go to riafo.users

6

by default cake inflects model class name to underscore table so your MdlUser get transformed into mdl_user table, you can change it in your model class using setTable method https://book.cakephp.org/3.0/en/orm/table-objects.html#basic-usage

7

Yes. That’s true. But my problem is that i have 2 databases. mdl and riafo. I want to insert mdl.mdl_user into riafo.users.
If a make a debug to $select, i get the data from mdl_user. The same for riafo.users. But when i try to execute the $query and i pass the values, it gets transformed to riafo.mdl_user, mixing the databases.

EDIT: Nevermind. I fixed it.