Miltiple database connections

Question

Miltiple database connections

Flavio Toscano May '20

Created May '20	Last Reply May '20	Replies 2	Views 200	Votes 0

Flavio Toscano 1.5k

May '20

Hi to everyone.

Let me try to explain my problem.

I´m using multiple databases in a project. One database that store all the users, and shared info like states and cities. And one database for each user, based on their id. Let´s use the config below:

DB1 Provider name: db Database name: app Table 1: States - [id, name] Table 2: Cities - [id, state_id, name]

DB2 Provider name: dbclient Database name: c1 (user id 1) Table 1: Clients - [id, city_id, name]

When i try to create a pagination with querybuilder below:

$builder = $this ->modelsManager ->createBuilder() ->from(Clients::class) ->innerJoin(Cities::class) ->where( "App\Models\Clients.name like '%" . $name . "%'" );

I´m getting this error: "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'c1.cities' doesn't exist"

Is there some way to create a Join between these two databases?

Dylan Anderson
125.7k

Accepted
answer

May '20

Joining between 2 separate databases is technically possible, but I really have to question why? There's no performance benefit and it causes a lot of problems - like this one.

Anyway, to answer your question: I don't think you can use QueryBuilder because I don't think PHQL resolves to SQL with database name, just table name. You should be able to accomplish what you want by writing raw SQL, then using the DB service to execute it. Whichever DB provider you use will need to be using a database account that has access to both privileges. In your query, you'll need to specify the database along with the table name:

SELECT
  *
FROM
  users.clients.name like :name:
WHERE
  users.client.class = shared.cities.class

Also, you should never be putting a variable into a query like you're doing with '%" . $name . "%'" - it's way too easy to fall victim to SQL injection attacks. You'll notice in my query how I used :name: as a token? That's a prepared query that could be executed with:

$DB->execute($query,['name'=>'%'.$name.'%']);

Doing it this way, the SQL server parses the query first, then injects your values into the parsed query. Prepared queries avoids bad strings from affecting the parser.

Flavio Toscano · Answer 1 · 2020-05-07T19:14:32-07:00

Thanks for the answer, i´ll try this approuch.

Let´s say that a client want´s to recovery his data from 2 days ago. Having multiple databases, in this case, make things easier. There are some other benefits too, but makes the maintenance more difficult.

And i´ll certainly change my code to bind values to que query. Thaks again!

talal424 · Answer 2 · 2020-05-12T17:50:05-07:00

if you want to simply get records from the old database and then create these records on the new database i suggest you set the old database as the db service and get your records then overwrite the db service with a new one using the new database and then add your records

i faced an issue like this one before so this could help

// in controller
public function setConnection($database)
{
    $this->getDI()->remove('db');

    $this->getDI()->set(
        'db',
        function () use ($db) {

            $connection = new Mysql(['host' => 'localhost','username' => 'root','password' => '','dbname' => $database,'charset' => 'utf8']);

            return $connection;
        },
        true
    );
}

public function doSomething()
{
    $oldRobots = Robot::find(); // this is the initial db

    // do something

    $this->setConnection('recent_database');

    foreach($oldRobots->toArray() as $robot) {

        $newRecord = new Robot($robot);

        $newRecord->save();
    }
}