Doctrine 2 Native Query从加入的实体中选择 [英] Doctrine 2 Native Query select from joined entity

问题描述

注意： 这是本机查询具体

我有2个相关引用 CrmBusinessPartner 和 RefCountry 和 CrmBusinessPartnerRepository 存储库。

I have 2 related entites "CrmBusinessPartner" and "RefCountry" and "CrmBusinessPartnerRepository" repository.

实体

CrmBusinessPartner entity

/**
 * CrmBusinessPartner
 *
 * @ORM\Table(name="crm_business_partner")
 * @ORM\Entity(repositoryClass="CrmBusinessPartnerRepository")
 * @Gedmo\SoftDeleteable(fieldName="deletedAt", timeAware=false)
 * @ExclusionPolicy("none")
 */
class CrmBusinessPartner
{
    /**
     *
     * @var integer
     *
     * @ORM\ManyToOne(targetEntity="RefCountry", inversedBy="businessPartners")
     * @ORM\JoinColumn(name="ref_country_id", referencedColumnName="id")
     */
    private $billingCountry;
}

RefCountry

RefCountry entity

/**
 * RefCountry
 *
 * @ORM\Table(name="ref_country")
 * @ORM\Entity
 */
class RefCountry
{
    /**
     * @ORM\OneToMany(targetEntity="CrmBusinessPartner", mappedBy="billingCountry")
     */
    private $businessPartners;

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

在 CrmBusinessPartnerRepository 存储库有这个工作代码：

In "CrmBusinessPartnerRepository" repository there is this working code:

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();

$rsm->addEntityResult('Entity\CrmBusinessPartner', 'bp');

$rsm->addFieldResult('bp','id','id');
$rsm->addFieldResult('bp','vat','vat');
$rsm->addFieldResult('bp','business_partner_name','name');

$rsm->addJoinedEntityResult('Entity\RefCountry', 'rc', 'bp', 'billingCountry');
$rsm->addFieldResult('rc','ref_country_name','name');

$em = $this->getEntityManager()
    ->createNativeQuery('
        SELECT
          bp.id,
          bp.vat,
          bp.name AS business_partner_name,
          rc.name AS ref_country_name
        FROM crm_business_partner bp
        LEFT JOIN ref_country rc ON bp.ref_country_id=rc.id
        ',
        $rsm
    );

return $em->getResult();

数据库是同步的（根据原则：schema：update）：

Database is in sync (according to doctrine:schema:update):

$ php console doctrine:schema:update
Nothing to update - your database is already in sync with the current entity metadata.

这是结果转储：

array ()
  0 => 
    object(Entity\CrmBusinessPartner)
      private 'id' => int 42
      private 'vat' => string '12345678998' (length=11)
      private 'name' => string 'Name 1' (length=22)
      private 'billingCountry' => null

---

QUESTION：

我不明白为什么billingCountries对所有的内容都无效？

编辑：

我已经有QueryBuilder的工作解决方案：

I already have working solution with QueryBuilder:

$qb = $this->getEntityManager()->createQueryBuilder('bp');
$qb->select(array('bp'));
$qb->from('AppBundle:CrmBusinessPartner', 'bp');
$qb->leftJoin('bp.billingCountry', 'rc');

return $qb->getQuery()->getResult();

编辑2：

CrmBusinessPartnerRepository 存储库，使用 ResultSetMappingBuilder 而不是 ResultSetMapping ，这是一个工作代码：

"CrmBusinessPartnerRepository" repository using ResultSetMappingBuilder instead of ResultSetMapping and this is a working code:

$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addRootEntityFromClassMetadata(
    'Entity\CrmBusinessPartner', 
    'bp'
);
$rsm->addJoinedEntityFromClassMetadata(
    'Entity\RefCountry', 
    'rc', 
    'bp', 
    'billingCountry', 
    array(
        'id' => 'billing_country_id', 
        'name'=>'rc_name'
    )
);
$em = $this->getEntityManager()
    ->createNativeQuery('
        SELECT
          bp.id,
          bp.vat,
          bp.name AS business_partner_name,
          rc.name AS ref_country_name
        FROM crm_business_partner bp
        LEFT JOIN ref_country rc ON bp.ref_country_id=rc.id
        ',
        $rsm
    );

return $em->getResult();

结果是相同的 billingCountry 对于所有实体都是空的：

Result is same billingCountry is allways null for all entities:

array ()
  0 => 
    object(Entity\CrmBusinessPartner)
      private 'id' => int 42
      private 'vat' => string '12345678998' (length=11)
      private 'name' => string 'Name 1' (length=22)
      private 'billingCountry' => null

我想知道为什么/如何/不（不）工作/做（它使用Native Query ？

推荐答案

好吧，好吧，看起来你必须添加 rc.id AS ref_country_id，在查询和 $ rsm-> addFieldResult（'rc'，'ref_country_id'，'id'）; code>这样：

Well, well, well...it looks like you have to add rc.id AS ref_country_id, in query and $rsm->addFieldResult('rc','ref_country_id','id'); like this:

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();

$rsm->addEntityResult('Entity\CrmBusinessPartner', 'bp');

$rsm->addFieldResult('bp','id','id');
$rsm->addFieldResult('bp','vat','vat');
$rsm->addFieldResult('bp','business_partner_name','name');

$rsm->addJoinedEntityResult('Entity\RefCountry', 'rc', 'bp', 'billingCountry');
$rsm->addFieldResult('rc','ref_country_id','id');
$rsm->addFieldResult('rc','ref_country_name','name');

$em = $this->getEntityManager()
    ->createNativeQuery('
        SELECT
          bp.id,
          bp.vat,
          bp.name AS business_partner_name,
          rc.id AS ref_country_id,
          rc.name AS ref_country_name
        FROM crm_business_partner bp
        LEFT JOIN ref_country rc ON bp.ref_country_id=rc.id
        ',
        $rsm
    );

return $em->getResult();

正如预期的那样工作。

我希望有人告诉我（Doctrine文件...看着你）：D

I wish somebody told me this before (Doctrine documentation...watching you) :D

这篇关于Doctrine 2 Native Query从加入的实体中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！