php - 使用 createNativeQuery 连接两个没有外键的实体

Question

我想根据一个字段(类型)的值从两个不同的表中检索数据。基本上，如果类型是 2 或 3，则将“subcategory_id”分别关联到类别 2 或类别 3

普通 SQL:

SELECT s.id, s.subcategory_id, s.type, IF(s.type = 2, c2.name, c3.name) as name
                                   FROM show_subcategory s
                                   LEFT JOIN category2 c2 ON (s.type = 2 AND s.subcategory_id = c2.id)
                                   LEFT JOIN category3 c3 ON (s.type = 3 AND s.subcategory_id = c3.id)
                                   WHERE s.category1_id = 1 ORDER BY s.order_list

将 DQL 转换为 Repository 类:

$em = $this->getEntityManager();
    $rsm = new ResultSetMapping;
    $rsm->addEntityResult('Acme\CoreBundle\Entity\ShowSubcategory', 's');
    $rsm->addFieldResult('s', 'id', 'id');
    $rsm->addFieldResult('s', 'subcategory_id', 'subcategoryId');
    $rsm->addFieldResult('s', 'type', 'type');
    $rsm->addFieldResult('s', 'order_list', 'orderList');

    $rsm->addJoinedEntityResult('Acme\CoreBundle\Entity\Category2' , 'c2', 's', 'category2');
    $rsm->addFieldResult('c2', 'id', 'id');
    $rsm->addFieldResult('c2', 'name', 'name');

    $rsm->addJoinedEntityResult('Acme\CoreBundle\Entity\Category3' , 'c3', 's', 'category3');
    $rsm->addFieldResult('c3', 'id', 'id');
    $rsm->addFieldResult('c3', 'name', 'name');

     $q = $em->createNativeQuery("SELECT s.id, s.subcategory_id, s.type, IF(s.type = 2, c2.name, c3.name) as name
                                   FROM show_subcategory s
                                   LEFT JOIN category2 c2 ON (s.type = 2 AND s.subcategory_id = c2.id)
                                   LEFT JOIN category3 c3 ON (s.type = 3 AND s.subcategory_id = c3.id)
                                   WHERE s.category1_id = ? ORDER BY s.order_list", $rsm);
    $q->setParameter(1, $category1);

    return $q->getResult();

在 ShowSubcategory 的实体中，我添加了两个新属性来处理此查询(类别 2、类别 3):

/**
 * @var integer $id
 *
 * @ORM\Column(name="id", type="integer", nullable=false)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @var integer $subcategoryId
 *
 * @ORM\Column(name="subcategory_id", type="integer", nullable=false)
 */
protected $subcategoryId;

/**
 * @var smallint $type
 *
 * @ORM\Column(name="type", type="smallint", nullable=false)
 */
protected $type;

/**
 * @var smallint $orderList
 *
 * @ORM\Column(name="order_list", type="smallint", nullable=true)
 */
protected $orderList;

/**
 * @var \Category1
 *
 * @ORM\ManyToOne(targetEntity="Acme\CoreBundle\Entity\Category1")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="category1_id", referencedColumnName="id")
 * })
 */
protected $category1;

/**
 * @var category2
 *
 * @ORM\ManyToOne(targetEntity="Acme\CoreBundle\Entity\Category2")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="subcategory_id", referencedColumnName="id")
 * })
 * @Assert\Blank()
 */
protected $category2;

/**
 * @var category3
 *
 * @ORM\ManyToOne(targetEntity="Acme\CoreBundle\Entity\Category3")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="subcategory_id", referencedColumnName="id")
 * })
 * @Assert\Blank()
 */
protected $category3;


/**
 * Get id
 *
 * @return integer
 */
public function getId()
{
    return $this->id;
}


/**
 * Set category1
 *
 * @param \Acme\CoreBundle\Entity\Category1 $category1
 * @return ShowSubcategory
 */
public function setCategory1(\Acme\CoreBundle\Entity\Category1 $category1 = null)
{
    $this->category1 = $category1;

    return $this;
}

/**
 * Get category1
 *
 * @return Acme\CoreBundle\Entity\Category1
 */
public function getCategory1()
{
    return $this->category1;
}

/**
 * Set subcategoryId
 *
 * @param integer $subcategoryId
 * @return ShowSubcategory
 */
public function setSubcategoryId($subcategoryId)
{
    $this->subcategoryId = $subcategoryId;

    return $this;
}

/**
 * Get subcategoryId
 *
 * @return integer
 */
public function getSubcategoryId()
{
    return $this->subcategoryId;
}

/**
 * Set type
 *
 * @param smallint $type
 * @return ShowSubcategory
 */
public function setType($type)
{
    $this->type = $type;

    return $this;
}

/**
 * Get type
 *
 * @return smallint
 */
public function getType()
{
    return $this->type;
}

/**
 * Set order
 *
 * @param smallint $OrderList
 * @return ShowSubcategory
 */
public function setOrderList($orderList)
{
    $this->orderList = $orderList;

    return $this;
}

/**
 * Get OrderList
 *
 * @return smallint
 */
public function getOrderList()
{
    return $this->orderList;
}

public function getCategory2()
{
    return $this->category2;
}

public function setCategory2(Category2 $category2 = null)
{
    $this->category2 = $category2;
    return $this;
}

public function getCategory3()
{
    return $this->category3;
}

public function setCategory3(Category3 $category3 = null)
{
    $this->category3 = $category3;
    return $this;
}

我得到这个错误:

Notice: Undefined index: id in /var/www/project/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php line 2402 

我不知道哪里出了问题我尝试了几个想法，但似乎没有任何效果。有什么建议吗？提前致谢。

Answer 1

我想您必须在 SELECT 子句中显式添加 id :

SELECT s.id, s.subcategory_id, s.type, IF(s.type = 2, c2.name, c3.name) as name, c2.id as c2id, c3.id as c3id
    FROM show_subcategory s
    LEFT JOIN category2 c2 ON (s.type = 2 AND s.subcategory_id = c2.id)
    LEFT JOIN category3 c3 ON (s.type = 3 AND s.subcategory_id = c3.id)
    WHERE s.category1_id = ? ORDER BY s.order_list

并调整结果集如下:

$rsm = new ResultSetMapping;
$rsm->addEntityResult('Acme\CoreBundle\Entity\ShowSubcategory', 's');
$rsm->addFieldResult('s', 'id', 'id');
$rsm->addFieldResult('s', 'subcategory_id', 'subcategoryId');
$rsm->addFieldResult('s', 'type', 'type');
$rsm->addFieldResult('s', 'order_list', 'orderList');

$rsm->addJoinedEntityResult('Acme\CoreBundle\Entity\Category2' , 'c2', 's', 'category2');
$rsm->addFieldResult('c2', 'c2id', 'id');
$rsm->addFieldResult('c2', 'name', 'name');

$rsm->addJoinedEntityResult('Acme\CoreBundle\Entity\Category3' , 'c3', 's', 'category3');
$rsm->addFieldResult('c3', 'c3id', 'id');
$rsm->addFieldResult('c3', 'name', 'name');