[Doctrine][Symfony] 单个注解中实现多表关联查询是否可行？

Hey there! Let's tackle this multi-table association problem with Doctrine annotations in your Symfony 3.4 project. Since you're working with a fixed, unmodifiable database schema, we'll focus on correctly mapping the existing relationships step by step to get from Table A's ID to Table E's libelle field.

First: Define the Entity Relationships with Annotations #

Let’s assume your table chain follows a typical nested structure (adjust based on your actual database schema): A → B → C → D → E. Below are the core annotation mappings for each entity, focusing on the associations that link them together.

Entity for Table A (TableA) #

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity
 * @ORM\Table(name="table_a")
 */
class TableA
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    // Example: One-to-Many relationship with Table B
    /**
     * @ORM\OneToMany(targetEntity="TableB", mappedBy="tableA")
     */
    private $tableBs;

    public function __construct()
    {
        $this->tableBs = new ArrayCollection();
    }

    // Getters & Setters
    public function getId(): ?int
    {
        return $this->id;
    }

    public function getTableBs()
    {
        return $this->tableBs;
    }
}

Entity for Table B (TableB) #

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="table_b")
 */
class TableB
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    // Many-to-One back to Table A
    /**
     * @ORM\ManyToOne(targetEntity="TableA", inversedBy="tableBs")
     * @ORM\JoinColumn(name="table_a_id", referencedColumnName="id")
     */
    private $tableA;

    // Example: One-to-One relationship with Table C
    /**
     * @ORM\OneToOne(targetEntity="TableC", mappedBy="tableB")
     */
    private $tableC;

    // Getters & Setters
    public function getTableC(): ?TableC
    {
        return $this->tableC;
    }
}

Entity for Table C (TableC) #

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity
 * @ORM\Table(name="table_c")
 */
class TableC
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    // One-to-One back to Table B
    /**
     * @ORM\OneToOne(targetEntity="TableB", inversedBy="tableC")
     * @ORM\JoinColumn(name="table_b_id", referencedColumnName="id")
     */
    private $tableB;

    // Example: Many-to-Many with Table D (using JoinTable as you mentioned)
    /**
     * @ORM\ManyToMany(targetEntity="TableD")
     * @ORM\JoinTable(
     *     name="table_c_d_junction",
     *     joinColumns={@ORM\JoinColumn(name="table_c_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="table_d_id", referencedColumnName="id")}
     * )
     */
    private $tableDs;

    public function __construct()
    {
        $this->tableDs = new ArrayCollection();
    }

    // Getters & Setters
    public function getTableDs()
    {
        return $this->tableDs;
    }
}

Entity for Table D (TableD) #

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="table_d")
 */
class TableD
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    // Many-to-One relationship with Table E
    /**
     * @ORM\ManyToOne(targetEntity="TableE")
     * @ORM\JoinColumn(name="table_e_id", referencedColumnName="id")
     */
    private $tableE;

    // Getters & Setters
    public function getTableE(): ?TableE
    {
        return $this->tableE;
    }
}

Entity for Table E (TableE) #

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="table_e")
 */
class TableE
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @ORM\Column(type="string", name="libelle")
     */
    private $libelle;

    // Getters & Setters
    public function getLibelle(): ?string
    {
        return $this->libelle;
    }
}

Second: Fetch Table E's libelle from Table A's ID #

Once your entities are mapped correctly, you can retrieve the libelle in two efficient ways:

Option 1: Traverse Entity Associations (Lazy Loading) #

If you already have a TableA instance, you can navigate through the nested associations. Note: This may trigger multiple queries if lazy loading is enabled—use fetch joins for better performance.

// Get TableA by ID
$tableA = $entityManager->getRepository(TableA::class)->find($yourAId);

// Traverse the association chain (adjust based on your relationship cardinality)
foreach ($tableA->getTableBs() as $tableB) {
    $tableC = $tableB->getTableC();
    foreach ($tableC->getTableDs() as $tableD) {
        $libelle = $tableD->getTableE()->getLibelle();
        // Use the $libelle value as needed
    }
}

Option 2: Use DQL/QueryBuilder for a Single Query #

To avoid the N+1 query problem, write a query that joins all necessary tables in one go:

Using DQL:

$dql = 'SELECT e.libelle
        FROM AppBundle\Entity\TableA a
        JOIN a.tableBs b
        JOIN b.tableC c
        JOIN c.tableDs d
        JOIN d.tableE e
        WHERE a.id = :aId';

$query = $entityManager->createQuery($dql)
    ->setParameter('aId', $yourAId);

$libelles = $query->getResult();
// $libelles is an array of libelle strings

Using QueryBuilder:

$qb = $entityManager->createQueryBuilder();
$libelles = $qb->select('e.libelle')
    ->from(TableA::class, 'a')
    ->join('a.tableBs', 'b')
    ->join('b.tableC', 'c')
    ->join('c.tableDs', 'd')
    ->join('d.tableE', 'e')
    ->where('a.id = :aId')
    ->setParameter('aId', $yourAId)
    ->getQuery()
    ->getResult();

Key Tips for Success #

• Match Your Actual Schema: Adjust relationship types (OneToMany, ManyToOne, etc.) and column names in @ORM\JoinColumn to exactly match your database's foreign keys and table structures.
• Clear Doctrine Cache: After updating annotations, run php bin/console doctrine:cache:clear-metadata in Symfony 3.4 to ensure the new mappings are loaded.
• Fetch Joins for Performance: Always use explicit joins in queries when accessing nested associations to prevent lazy loading extra database calls.

内容的提问来源于stack exchange，提问作者Kraighh