Implement cross database routing

Need Help
1

Let’s say my application has many databases.

One shared database with 2 tables:

  • Users: id, email, password and company_id
  • Company: id, name and database

Each company has its own database with a table called Records, all of them with the exact same structure.

  • Records: id, date, score

This is my working code

AppController.php:

$identity = $this->Authentication->getIdentity();

if ($identity) {
    Configure::write("database", $identity->get('company')->get('database'));
}

RecordsTable.php

public static function defaultConnectionName(): string
{
    return Configure::read("database");
}

When a user logs in, they can see the records of their own company.

The link example.com/records might connect to a different database and thereby show different results according to each user.

My problem is that some users want to access data from another companies, so I’m trying to use the prefix to define which database should I use. For example:

Has anyone solved a similar problem? I would like to know how to approach since I cant find the right tools on routes.php.

Thanks in advance!

2

So company id = 1 has records stored in one database and company id = 2 has records stored in a different data base?

Each Table class has the Table::setConnection() method available. This should allow you to point the Table to the correct db on the fly.

Normally a Controller will set up its expected table using the configured db. But you could defer creation of the table until the controller sees what the Request is. Then you can initialize your Table properly.

2 Likes
3

Yes, exactly like that.

Thanks for your response! your comment is really helpful. I still have to find how can I route each number prefix to the controller and how to defer the table creation. I will update this comment when I find a good solution

4

And out-of-the-box CakePHP url looks like this:

host/yourController/yourAction/optional/values

Which would go to your controller action as:

class YourController extends Controller
{

   public function yourAction($optional, $values) {
      //logic here
   }

}

If you follow this pattern you probably won’t need to make any new routes or do anything fancy.

1 Like