symfony - 在 Dotrine 中查询连接表

Question

我有以下两个类，它们具有ManyToMany关系，并保存在名为countries_involved的连接表中。

class CountriesInvolved
{
/**
 * @var integer
 */
private $id;

/**
 * @var string
 */
private $description;

/**
 * @var \DateTime
 */
private $createdAt;

/**
 * @var \DateTime
 */
private $updatedAt;

/**
 * @var \ACME\SouthBundle\Entity\Country
 */
private $country;

/**
 * @var \Doctrine\Common\Collections\Collection
 */
private $involvement;
}

和

class Involvement
{
/**
 * @var integer
 */
private $id;

/**
 * @var string
 */
private $name;

/**
 * @var string
 */
private $description;
}

YML中的关系定义如下

manyToMany:
    involvement:
        targetEntity:   Involvement
        joinTable:
            name: countries_involvement
            joinColumns:
                case_country_involved_id:
                    referencedColumnName:   id
            inverseJoinColumns:
                involvement_id:
                    referencedColumnName:   id

我试图根据参与的 id 返回参与的国家/地区的结果，但在编写查询时有点卡住而没有出现错误。这是我迄今为止尝试过的:

$em = $this->getDoctrine()->getManager()->createQueryBuilder();
    $q = $em->select('c')
    ->from('ACMESouthBundle:CountriesInvolved','c')
    ->innerJOIN('c.Involvement','i')
    ->where('i.id = 1') 
    ->groupBy('c.country')->getQuery();

错误是:

[Semantical Error] line 0, col 80 near 'i WHERE i.id': Error: Class ACME\SouthBundle\Entity\CountriesInvolved has no association named Involvement

Answer 1

首先，我建议使用注释，您的代码将更加可读。

我认为问题是您忘记了 inversedBy 和 mappedBy 属性。

以下代码是使用注释解决您的问题的可能解决方案。

您应该将此代码添加到Involvement 实体中:

/**
 * @ORM\ManyToMany(targetEntity="CountriesInvolved", inversedBy="involvement")
 * @ORM\JoinTable(name="countries_involvement",
 *      joinColumns={@ORM\JoinColumn(name="case_country_involved_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="involvement_id", referencedColumnName="id")}
 *      )
 */
 private $countries;

在 CountriesInvolved 实体中，您应该在 $involvement 中添加以下注释:

/**
 * @ORM\ManyToMany(targetEntity="Involvement", mappedBy="countries")
 */
 private $involvement;

我刚刚重写了查询，如下所示:

$em = $this->getEntityManager();

$query = $em->createQuery('
    SELECT c
    FROM ACMESouthBundle:CountriesInvolved c
    JOIN c.involvement i
    WHERE i.id = :id
');

$query->setParameter('id', '1');

return $query->getResult();

编辑

这是使用 YAML 方法:

CountriesInvolved:
    manyToMany:
        involvement:
            targetEntity: Involvement
            mappedBy: countries

CountriesInvolved:
    manyToMany:
        countries:
            targetEntity: CountriesInvolved
            inversedBy: involvement
            joinTable:
                name: countries_involvement
                joinColumns:
                    case_country_involved_id:
                        referencedColumnName: id
                inverseJoinColumns:
                    involvement_id:
                        referencedColumnName: id