I'm having some trouble trying to achieve multiple connection to database in some clean way. Keep in mind that this is my first symfony project ever, and i'm only a young developer.

In my project, the goal is to be able to select a client, with a specific database, and to connect to the database to be able to export some datas.

I tried to do the solution describe in this post Symfony 3 connection to multiple databases and i tried to generate dynamically an entityManager.

So i created a factory EntityManagerFactory :

Factory\EntityManagerFactory

<?php

namespace App\Factory;

use Doctrine\ORM\Tools\Setup;
use Doctrine\ORM\EntityManager;
use Symfony\Component\Yaml\Yaml;

class EntityManagerFactory {

    private $config_db_group;

    public function __construct(string $config_db_group) {
        $this->config_db_group = $config_db_group;
    }

    public function createManager($idDb) {

        $isDevMode = false;
      
        $config = Setup::createAnnotationMetadataConfiguration(array(__DIR__ . "/src"), $isDevMode);
        

        $connectionConfig = $this->getConfigDb($idDb);

        $dbParams = [
            'driver' => 'pdo_mysql',
            'host' => $connectionConfig['host'],
            'username' => $connectionConfig['user'],
            'password' => $connectionConfig['password'],
            'dbname' => $connectionConfig['db_name']
        ];

        return EntityManager::create($dbParams, $config);
    }

    private function getConfigDb($idDb) {
        $connectionConfig = Yaml::parseFile("$this->config_db_group");
        return $connectionConfig[$idDb];
    }
}

I have a yaml that describes the connection config :

config\dbgroup.yaml

1:
  db_name: "db_name1"
  host: "host1"
  user: "user1"
  password: "password1"
  port: "3306"
2:
  db_name: "db_name2"
  host: "host2"
  user: "user2"
  password: "password2"
  port: "3306"

In my config\services.yaml, i did something that was described in the post.

# Create a service for the factory
    App\Factory\EntityManagerFactory:
        arguments:
            $config_db_group: '%kernel.project_dir%\config\db_group.yaml'
            
    # Use the factory service as the first argument of the 'factory' option
    # and the factory method as the second argument
    App\Factory\EntityManager:
        factory: ['@App\Factory\EntityManagerFactory', 'getManager']

I don't really understand what this is, i think this defines my factory as a service ? ...

And then i try to create an entityManager in my controller, this was just to test if it works, i think database request should be in a Repository, or a Services ?

<?php

namespace App\Controller;

use Twig\Environment;
use App\Factory\EntityManagerFactory;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use App\Repository\Istrator\DatabaseGroupRepository;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;

class DashboardController extends AbstractController {

    private $twig;
    private $databaseGroupRepository;
    private $factory;

    public function __construct(Environment $twig, DatabaseGroupRepository $databaseGroupRepository, EntityManagerFactory $factory) {
        $this->twig = $twig;
        $this->databaseGroupRepository = $databaseGroupRepository;
        $this->factory = $factory;
    }

    #[Route('{slug}/dashboard', name: 'app_dashboard')]
    public function index(string $slug): Response {
        
        // I get the specific database
        $databaseGroup = $this->databaseGroupRepository->findBySlug($slug);

        // Then i try to create an entityManager with the correct config
        $entityManager = $this->factory->createManager($databaseGroup->getIdDb());
        
        // Then just to try my connection, i create a basic query
        $rsm = new ResultSetMapping();
        $test = $entityManager->createNativeQuery("USE mydatabase; SELECT * FROM mytable", $rsm)->execute();
        
        return new Response($this->twig->render('dashboard/dashboard.html.twig', [
            'controller_name' => 'DashboardController',
        ]));
    }
    
}

For now it doesn't work.. But i have several questions :

• The databases i try to connect are not database with the same database schemes than my actual database. They are external database. Should i create Entities, and repository to manage them ? Or should i just do some connection, some request without entity and repository ?
• In the stackoverflow post that i based my code on, there is a second way of doing it, by defining all future connection in a doctrine.yaml. I have a defined number of connection but like 50 or something, should i do this instead of creating entityManager dynamically ?

As you can see, i'm a bit confused right now but if someone could tell me their point of vue, it would be great.

If you need any other information, just tell me !

Thanks in advance

EDIT : I found the solution, and it was really stupid :

In my EntityManagerFactory, i did this :

$dbParams = [
            'driver' => 'pdo_mysql',
            'host' => $connectionConfig['host'],
            // IT'S NOT USERNAME, IT'S USER ....
            'username' => $connectionConfig['user'],
            'password' => $connectionConfig['password'],
            'dbname' => $connectionConfig['db_name']
        ];

In the StackOverflow post that is used to create this factory, it was written username, but the correct field was user.

That was my first mistake, my second mistake is that, when i tried to execute my nativeQuery, I created a resultSetMapping empty :

 $entityManager = $this->factory->createManager($databaseGroup->getIdDb());
        
        // I did this
        $rsm = new ResultSetMapping();
        $test = $entityManager->createNativeQuery("USE mydatabase; SELECT * FROM mytable", $rsm)->execute();

        // I SHOULD HAVE DONE THIS
        $rsm = new ResultSetMappingBuilder($entityManager);
        $rsm->addScalarResult('id', 'id');
        [... for every field]
        $result = $entityManager->createNativeQuery("SELECT id, prenom, nom FROM mytable",$rsm)->execute();

I use addScalarResult because what i get from those databases are not Entities I will keep in my program.

I hope if someone get stuck like me, this could help him/her/etc..