查询原则中的加入表 [英] Querying Join Table in Doctrine

问题描述

我有以下两个类别，它们有一个多个关系，并保存在名为 countries_involved 的加入表中。

  class CountriesInvolved 
 {
 / ** 
 * @var整数
 * / 
私人$ 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：
参与：
 targetEntity：参与
 joinTable：
名称：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（）; 
  

错误是：

  [语义错误]行0，col 80附近'i WHERE i.id'：错误：类ACME\SouthBundle\Entity\CountriesInvolved没有关联名为Involvement 
  

解决方案

首先，我建议使用注释

我认为的问题是你忘记了$ code> inversedBy 和 mappedBy 属性。

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

您应该将此代码添加到 Involvement entity：

  / ** 
 * @ ORM\ManyToMany（targetEntity =CountriesInvolved，inversedBy =involvement）
 * @ ORM\JoinTable（name =countries_involvement 
 * joinColumns = {@ ORM\JoinColumn（name =case_country_involved_id，referencedColu mnName =id）}，
 * inverseJoinColumns = {@ ORM\JoinColumn（name =involvement_id，referencedColumnName =id）} 
 *）
 * / 
私人$国家; 
  

在 CountriesInvolved 实体中，您应该添加以下注释在 $参与：

  / ** 
 * @ 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：
参与：
 targetEntity：Involvement 
 mappedBy：countries 
 
 CountriesInvolved：
 manyToMany：
国家/地区：
 targetEntity：CountriesInvolved 
 inversedBy：involvement 
 joinTable：
 name ：countries_involvement 
 joinColumns：
 case_country_involved_id：
 referencedColumnName：id 
 inverseJoinColumns：
 involvement_id：
 referencedColumnName：id 
  

I have the following two classes which are have a ManyToMany relationship and saved in a joining table called 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;
}

and

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

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

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

The relationship is defined as below in YML

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

I'm trying to return results of countries involved based on the id of an involvement but kind of stuck in writing the query without getting an error. Here's what I tried thus far:

$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();

The error is:

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

解决方案

Firstly, I would recommend the use of annotations, your code will be more readable.

The problem I think is that you have forgotten inversedBy and mappedBy properties.

The following code is a possible solution to your problem using annotations.

You should add this code to Involvement entity:

/**
 * @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;

and in CountriesInvolved entity you should add the following annotations in $involvement:

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

I have just rewrite the query, something like this:

$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();

EDIT

This is with YAML method:

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

这篇关于查询原则中的加入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！