postgresqlsymfonydoctrine-ormdoctrinesymfony6

Symfony 6 doctrine issue on postgresql with multiple databases SQLSTATE[42P01]: Undefined table: 7 ERROR

My Symfony project (sf v6) uses postgresql/postgis and 2 databases: the main one with specific business datas and the second one called web-services database with commons datas share beetwen multiple projects (with two schema, mainly gis which contain GIS datas) So first, I refered to https://symfony.com/doc/current/doctrine/multiple_entity_managers.html
and set my config/package/doctrine.yaml :

doctrine:
    dbal:
        connections:
          default:
            url: '%env(resolve:DATABASE_URL)%'
            server_version: '13.5'
          web_services:
            url: '%env(resolve:DATABASE_WS_URL)%'
            server_version: '13.5'
        default_connection: default
    orm:
        default_entity_manager: default
        entity_managers:
            default:
              connection: default
              #auto_generate_proxy_classes: true # thow exception Unrecognized options
              #enable_lazy_ghost_objects: true # thow exception Unrecognized options
              report_fields_where_declared: true
              validate_xml_mapping: true
              naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
              auto_mapping: true
              mappings:
                  App:
                      is_bundle: false
                      dir: '%kernel.project_dir%/src/Entity'
                      prefix: 'App\Entity'
                      alias: App
                      type: attribute
              #profiling:            "%kernel.debug%" # thow exception Unrecognized options
              #profiling_collect_backtrace: '%kernel.debug%' # thow exception Unrecognized options
            web_services:
              connection: web_services
              mappings:
                  Web_services:
                      is_bundle: false
                      dir: '%kernel.project_dir%/src/Entity/WebServices'
                      prefix: 'App\Entity\WebServices'
                      alias: Web_services
                      type: attribute

when@test:
    doctrine:
        dbal:
            # "TEST_TOKEN" is typically set by ParaTest
            dbname_suffix: '_test%env(default::TEST_TOKEN)%'

when@prod:
    doctrine:
        orm:
            auto_generate_proxy_classes: false
            proxy_dir: '%kernel.build_dir%/doctrine/orm/Proxies'
            query_cache_driver:
                type: pool
                pool: doctrine.system_cache_pool
            result_cache_driver:
                type: pool
                pool: doctrine.result_cache_pool

    framework:
        cache:
            pools:
                doctrine.result_cache_pool:
                    adapter: cache.app
                doctrine.system_cache_pool:
                    adapter: cache.system

mainly, dbal url, and connection dir and prefix

I can access without any issue on default connection, entity, repository !

I defined two entities to access to web_services connection datas:
First one, Translate

// src/Entity/WebServices/Translate.php

namespace App\Entity\WebServices;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\Id;

#[ORM\Entity(repositoryClass: 'App\Repository\WebServices\TranslateRepository')]
#[ORM\Table(name: "gis.translate")]
class Translate
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $translateId;

    #[ORM\Column(type: 'string', length: 255)]
    private $frValue;

    #[ORM\Column(type: 'string', length: 255)]
    private $gbValue;

    public function getTranslateId(): ?int
    {
        return $this->translateId;
    }

    // and others getters/setters
}

Second one, Country

// src/Entity/WebServices/Country.php

namespace App\Entity\WebServices;

use App\Repository\WebServices\CountryRepository;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\Id;

#[ORM\Entity(repositoryClass: 'App\Repository\WebServices\CountryRepository')]
#[ORM\Table(name: "gis.country")]
class Country
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $countryId = null;

    #[ORM\Column(length: 2, nullable: true)]
    private ?string $isoCode = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: 'translate_id', referencedColumnName: 'translate_id', nullable: false)]
    private ?Translate $translate = null;

    public function getCountryId(): ?int
    {
        return $this->country_id;
    }

    // and others getters/setters
}

Note that web_services tables uses tablename_id, not simply id

At this point, if I run (in my sf docker container, postgresql in other one):
php bin/console dbal:run-sql 'SELECT * FROM translate' for main database translate table, or
php bin/console dbal:run-sql --connection web_services 'SELECT * FROM gis.translate' for web_services database translate table,
it runs correctly !

It runs correctly too in the LocationCountryController controller if I call getClassMetadata method of EntityManager, in src/Controller/WebServices/CountryController:

[…]
use Doctrine\ORM\EntityManagerInterface;
[…]

#[Route(path: '/location/country')]
class LocationCountryController extends AbstractController
{
    #[Route(path: '/', name: 'location_country_index', methods: ['GET'])]
    public function index(EntityManagerInterface $web_servicesEntityManager): Response
    {
        $ctableName = $web_servicesEntityManager->getClassMetadata(Country::class);
        $ttableName = $web_servicesEntityManager->getClassMetadata(Translate::class);
        dd($ctableName, $ttableName);
    }

    //[…]
}

If I run, always in src/Controller/WebServices/CountryController:

class LocationCountryController extends AbstractController
{
    #[Route(path: '/', name: 'location_country_index', methods: ['GET'])]
    public function index(EntityManagerInterface $web_servicesEntityManager): Response
    {
        $connection = $doctrine->getConnection('web_services');
        $countries = $connection->fetchAllAssociative('SELECT * FROM country c JOIN translate t ON c.translate_id=t.translate_id LIMIT 20');
        dd($countries);
    // […]

It returns too 20 countries with correct translate datas !

BUT as soon I want to use Country repository, for example, always in src/Controller/WebServices/CountryController:

namespace App\Controller;

use App\Entity\WebServices\Country;
use App\Entity\WebServices\Translate;
use App\Repository\WebServices\CountryRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\Persistence\ManagerRegistry;

#[Route(path: '/location/country')]
class LocationCountryController extends AbstractController
{
    #[Route(path: '/', name: 'location_country_index', methods: ['GET'])]
    public function index(ManagerRegistry $doctrine, EntityManagerInterface $web_servicesEntityManager): Response
    {
        $repository = $web_servicesEntityManager->getRepository(Country::class);
        $countries = $repository->findAll(); // ->find(1) returns same error
        dd($countries);
        // or
        $countriesRepository = $doctrine->getRepository(Country::class, 'web_services');
        dd($countriesRepository->findAll());
//[…]

I have the error:
An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "gis.country" does not exist

Here are my src/Repository/WebServices/CountryRepository.php:

namespace App\Repository\WebServices;

use App\Entity\WebServices\Country;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<Country>
 *
 * @method Country|null find($id, $lockMode = null, $lockVersion = null)
 * @method Country|null findOneBy(array $criteria, array $orderBy = null)
 * @method Country[]    findAll()
 * @method Country[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class CountryRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Country::class);
    }

    // […]

    /**
     * @return country[] Returns an array of Country objects
     */
    public function findAll(): array
    {
        return $this->createQueryBuilder('c')
//          ->innerJoin('c.translate', 't')
//          ->addSelect('t')
//            ->orderBy('t.frValue', 'ASC')
            ->setMaxResults(5)
            ->getQuery()
            ->getResult()
        ;
    }

I updaded symfony to last 6.3 release, last 2.17 doctrine and still have the same error !

In my searches to solve, I found this post : https://dba.stackexchange.com/questions/101570/error-42p01-relation-does-not-exist … bad permissions… but I can access to table if I don't use repository ! and if I search with "postgresql Error 42P01", I founded this post :
PostgreSQL ERROR: 42P01: relation "[Table]" does not exist and my translate and country table seems correctly named.

EDIT: I tried in entity to separate schema and table:
#[ORM\Table(schema: "sig", name: "country")] , it doesn't change !

Solution

  • The problem come from the configuration of the registry. Symfony auto-wiring will search the first pattern which match the configuration. In this case App\Entity will always come first.

    There are two solutions to this problem :

    # config/package/doctrine.yaml
# […]
   orm:
     default_entity_manager: default
     entity_managers:
       web_services:
         connection: web_services
         mappings:
           Web_services:
             is_bundle: false
             dir: '%kernel.project_dir%/src/Entity/WebServices'
             prefix: 'App\Entity\WebServices'
             alias: Web_services
             type: attribute
       default:
         connection: default
         report_fields_where_declared: true
         validate_xml_mapping: true
         naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
         auto_mapping: true
         mappings:
           App:
             is_bundle: false
             dir: '%kernel.project_dir%/src/Entity'
             prefix: 'App\Entity'
             alias: App
             type: attribute
# […]