symfony2 dynamic database connection using doctrin

Question

I am trying to have multiple database connection in Symfony 2 with doctrine but not able to do so.

I have searched extensively in Google and stack-overflow but everywhere it's done via config.yml file or dynamic database where all DB's have same schema/entities .

But for my case the database is determined based on subdomain and the database schema is not same for all subdomains.

Ex:
test1.example.com => Should load test1 db
test2.example.com => Will load test2 db

Both test1 and test2 DB are different are created at DB level and not having entity entries in doctrine.

Can anyone please help me how to do this in Symfony 2.

Answer 1

Thanks to byf-ferdy (https://stackoverflow.com/a/20444097/2976700) , i am able to figure out how to use other DB having no doctrine entities. Just use the following code in your action controller

$connectionFactory = $this->get('doctrine.dbal.connection_factory');                
$connection = $connectionFactory->createConnection(
                array('pdo' => new \PDO("mysql:host=$hostname;dbname=$dbname", 
                       $username,$password))
 );
 $query = $connection->executeQuery('SELECT * FROM multi_client');
 $results = $query->fetchAll();

To know the subdomain accessed one can use $domain = $request->getHost();

Accordingly one change change the DB name and other parameters. Hope it helps others

Answer 2

It seems to me that using Doctrines ODM is not the right way to approach this. You can still use Doctrine to connect to databases and query them. But if you have no entity classes the use of an entity manager seems to be inappropriate.

Use Doctrine for Connection handling

Here is how you create a connection to a Database with the doctrine Connection class:

/** @var \Doctrine\Bundle\DoctrineBundle\ConnectionFactory $connectionFactory */
$connectionFactory = $this->getContainer()->get('doctrine.dbal.connection_factory');
$connection = $connectionFactory->createConnection(
    array('pdo' => new \PDO("mysql:host=$hostname;dbname=$dbname", $username, $password))
);

Now you can use $connection as a simple PDO object:

$connection->executeQuery('SELECT * FROM your_table');

You could add this code as a service to make it accessible everywhere.
If you want to connect to a different database for a different domain you can use this code to identify the domain:

$this->getRequest()->getHost();

To access the domain in an action do this:

public function yourAction(Request $request, /* ... */)
{
    // the Controller extends the Container. So need to get it here:
    $connectionFactory = $this->get('doctrine.dbal.connection_factory');

    // also access the domain like this:
    $domain = $request->getHost();
}