Multiple database join query in Symfony-CodePudding

I am new to symfony and I need to join query from multiple database. I came across many similar questions, but I am not able to solve this problem. I tried to join query in repository and I get the error "the class 'App\Entity\customerdata\CustomerAction' was not found in the chain configured namespaces App\Entity\Steuer\StUserSendAuth ". Below I have added the code, used.

My doctrine.yaml

doctrine:
    dbal:
        default_connection: default
        connections:
            steuer:
                # configure these for your database server
                url: '%env(resolve:DATABASE_STEUER_URL)%'
                driver: 
            kundendaten:
                # configure these for your database server
                url: '%env(resolve:DATABASE_CUSTOMERDATA_URL)%'
                driver: 
            
    orm:
        default_entity_manager: default
        entity_managers:
            steuer:
                connection: steuer
                mappings:
                    Steuer:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Steuer'
                        prefix: 'App\Entity\Steuer'
                        alias: Steuern
            customerdata:
                connection: customerdata
                mappings:
                    Customerdata:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Customerdata'
                        prefix: 'App\Entity\Customerdata'
                        alias: Customerdata

First database Entity :-

namespace App\Entity\Steuer;

use Doctrine\ORM\Mapping as ORM;

/**
 * StUserSendAuth
 * @ORM\Entity(repositoryClass="App\Repository\Steuern\StUserSendAuthRepository")
 * @ORM\Table(name="st_user_send_auth")
 */
class StUserSendAuth
{
    /**
     * @var int
     *
     * @ORM\Column(name="abgabe_sendauth_id", type="integer", nullable=false, options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $abgabeSendauthId = '0';

    /**
     * @var int
     *
     * @ORM\Column(name="customeraction_id", type="integer", nullable=false, options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $CustomeractionId = '0';

Second Databse connection Entity :-

namespace App\Entity\customerdata;

use Doctrine\ORM\Mapping as ORM;

/**
 * CustomerAction
 * @ORM\Entity(repositoryClass="App\Repository\customerdata\CustomerActionRepository")
 * @ORM\Table(name="customer_action")
 */
class CustomerAction
{
    /**
     * @var int
     *
     * @ORM\Column(name="customeraction_id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $CustomeractionId;

Repository to join the query :-

namespace App\Repository\Steuer;

use App\Entity\Steuer\StUserSendAuth;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method StUserSendAuth|null find($id, $lockMode = null, $lockVersion = null)
 * @method StUserSendAuth|null findOneBy(array $criteria, array $orderBy = null)
 * @method StUserSendAuth[]    findAll()
 * @method StUserSendAuth[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 * @method StUserSendAuth|null findstatistikOnline()
 */
class StUserSendAuthRepository extends ServiceEntityRepository
{
    /**
     * @return StUserSendAuth[] Returns an array of StUserSendAuth objects
     */
    public function findstatistikOnline()
    {

        return $this->getEntityManager()
            ->createQueryBuilder('s')
            ->select('p.updatedAt')
            ->from('App\Entity\Steuern\StUserSendAuth', 'p')
            ->leftJoin('App\Entity\customerdata\CustomerAction', 'uas', 'WITH', 'uas.CustomeractionId = p.CustomeractionId')
            ->getQuery()
            ->getArrayResult()
        ;

Controller class : -

namespace App\Controller;

use App\Repository\Steuer\StUserSendAuthRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class OnlineAuswertungController extends AbstractController
{
    #[Route('/count_statistik/onlineAuswertung', name: 'onlineAuswertung')]
    public function index(StUserSendAuthRepositoryRepository $StUserSendAuthRepositoryRepository): Response
    {
$Table = $StUserSendAuthRepositoryRepository->findstatistikOnline();

        return $this->render('online_auswertung/index.html.twig', [
            'controller_name' => 'OnlineAuswertungController',
            'table' => $Table,
        ]);
    }
}

Please let me know how to do query with join on multiple databases. Thank you!

CodePudding user response：

1.IF you trying to achieve the multiple connection's for database then please refer this document. https://symfony.com/doc/current/doctrine/multiple_entity_managers.html.

Please validate the entity you have created in the project by doing schema validation.
If your trying to fetch from the two different table from same database.then syntax is here

example:

...
$this->getEntityManager()
        ->createQueryBuilder('s')
        ->select('p.updatedAt')
        ->from('Steuern:StUserSendAuth', 'p')
        ->leftJoin('customerdata:CustomerAction', 'uas', 'WITH', 'uas.CustomeractionId = p.CustomeractionId')
        ->getQuery()
        ->getArrayResult()

...

NOTE: please check this reference

CodePudding user response：

I finally got the solution to multiple database query in symfony.

First need to rename table names as databasename.tablename in all the entity class.

namespace App\Entity\Steuer;

use Doctrine\ORM\Mapping as ORM;

/**
 * StUserSendAuth
 * @ORM\Entity(repositoryClass="App\Repository\Steuer\StUserSendAuthRepository")
 * @ORM\Table(name="steuer.st_user_send_auth")
 */
class StUserSendAuth
{
    /**
     * @var int
     *
     * @ORM\Column(name="abgabe_sendauth_id", type="integer", nullable=false, options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $abgabeSendauthId = '0';

    /**
     * @var int
     *
     * @ORM\Column(name="customeraction_id", type="integer", nullable=false, options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $CustomeractionId = '0';

I tried using raw_sql to use in controller.

class OnlineAuswertungController extends AbstractController
{
    #[Route('/count_statistik/onlineAuswertung', name: 'onlineAuswertung')]
    public function index(Connection $connection): Response
    {
       $query = "SELECT p.updated_at
                 FROM steuer.st_user_pdfs_sendauth p
                  LEFT JOIN customerdata.CustomerAction sa on sa. customeraction_id = p.customeraction_id"
      $statement = $connection->prepare($query);
        $statement->execute();

        return $this->render('online_auswertung/index.html.twig', [
            'controller_name' => 'OnlineAuswertungController',
            'table' => $Table,
        ]);
    }
}

I was not able to use $statement->fetchAll(); as it is showing fetchAll() is deprecated. For now this code works fine.