mysql - 交响乐团 3 : ResultSetMapping generate Mapping Exception error

Question

我正在创建一个库存系统。我想使用 ResultSetMapping 显示库存中商品的余额。我按照http://doctrine-orm.readthedocs.io/en/latest/reference/native-sql.html#examples中显示的示例进行操作但我收到错误“类'RequestItem'不存在 - 500 内部服务器错误 - MappingException”

namespace ECA\InventoryBundle\Controller;

use Symfony\Component\HttpFoundation\Request;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use ECA\InventoryBundle\Entity\RequestItem;
use ECA\InventoryBundle\Form\RequestItemType;

use Symfony\Component\Form\Extension\Core\Type\TextType;

use Doctrine\ORM\Query\ResultSetMapping;

/**
 * RequestItem controller.
 *
 * @Route("/requestitem")
 */
class RequestItemController extends Controller
{
  /**
  * Calculate and display balance
  *
  *@Route("/balance", name="requestitem_balance")
  *@Method("GET")
  */
  public function balanceAction()
  {

      $rsm = new ResultSetMapping;
      $rsm->addEntityResult('RequestItem', 'r');
      $rsm->addFieldResult('r','item_id', 'id');
      $rsm->addScalarResult('SUM(r.quantity)', 'used');
      $rsm->addJoinedEntityResult('Item' , 'i', 'r','item');
      $rsm->addFieldResult('i', 'name','name');
      $rsm->addFieldResult('i', 'quantity','quantity');
      $rsm->addScalarResult('(i.quantity - SUM(r.quantity))', 'balance');

      $sql = "SELECT i.name, r.item_id, i.quantity, SUM(r.quantity) AS used,
      (i.quantity - SUM(r.quantity)) AS balance
      FROM request_item r  INNER JOIN item i  ON i.id=r.item_id GROUP BY r.item_id";


      $em = $this->getDoctrine()->getManager();

      $query = $em->createNativeQuery($sql, $rsm);

      $itemBalance = $query->getResult();

      $form = $this->createFormBuilder($itemBalance)
        ->add('item_id', TextType::class, array('attr' =>array('class' =>'form-control', 'style' =>'margin-bottom:15px')))
        ->add('name', TextType::class, array('attr' =>array('class' =>'form-control', 'style' =>'margin-bottom:15px')))
        ->add('quantity', TextType::class, array('attr' =>array('class' =>'form-control', 'style' =>'margin-bottom:15px')))
        ->add('used', TextType::class, array('attr' =>array('class' =>'form-control', 'style' =>'margin-bottom:15px')))
        ->add('balance', TextType::class, array('attr' =>array('class' =>'form-control', 'style' =>'margin-bottom:15px')))
        ->getForm();

      return $this->render('requestitem/balance.html.twig', array('itemBalance'=> $itemBalance, 'form' => $form->createView()));

  }

但是 RequestItem 类存在

namespace ECA\InventoryBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * RequestItem
 *
 * @ORM\Table(name="request_item")
 * @ORM\Entity(repositoryClass="ECA\InventoryBundle\Repository\RequestItemRepository")
 */
class RequestItem
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date", type="date")
     */
    private $date;

    /**
     * @var int
     *
     * @ORM\Column(name="quantity", type="integer")
     */
    private $quantity;

    /**
     * @var string
     *
     * @ORM\Column(name="client", type="string", length=255)
     */
    private $client;

    /**
     * @var string
     *
     * @ORM\Column(name="remark", type="text", nullable=true)
     */
    private $remark;

    /**
     * @var \ECA\InventoryBundle\Entity\Item
     *
     * @ORM\ManyToOne(targetEntity="ECA\InventoryBundle\Entity\Item")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="item_id", referencedColumnName="id")
     * })
     */
    private $item;


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

    /**
     * Set date
     *
     * @param \DateTime $date
     *
     * @return RequestItem
     */
    public function setDate($date)
    {
        $this->date = $date;

        return $this;
    }

    /**
     * Get date
     *
     * @return \DateTime
     */
    public function getDate()
    {
        return $this->date;
    }

    /**
     * Set quantity
     *
     * @param integer $quantity
     *
     * @return RequestItem
     */
    public function setQuantity($quantity)
    {
        $this->quantity = $quantity;

        return $this;
    }

    /**
     * Get quantity
     *
     * @return int
     */
    public function getQuantity()
    {
        return $this->quantity;
    }

    /**
     * Set client
     *
     * @param string $client
     *
     * @return RequestItem
     */
    public function setClient($client)
    {
        $this->client = $client;

        return $this;
    }

    /**
     * Get client
     *
     * @return string
     */
    public function getClient()
    {
        return $this->client;
    }

    /**
     * Set remark
     *
     * @param string $remark
     *
     * @return RequestItem
     */
    public function setRemark($remark)
    {
        $this->remark = $remark;

        return $this;
    }

    /**
     * Get remark
     *
     * @return string
     */
    public function getRemark()
    {
        return $this->remark;
    }

    /**
     * Set item
     *
     * @param \ECA\InventoryBundle\Entity\Item $item
     *
     * @return RequestItem
     */
    public function setItem(\ECA\InventoryBundle\Entity\Item $item = null)
    {
        $this->item = $item;

        return $this;
    }

    /**
     * Get item
     *
     * @return \ECA\InventoryBundle\Entity\Item
     */
    public function getItem()
    {
        return $this->item;
    }
}

我在 XAMPP (PHP7) 上使用 Symfony3。我已经在 Mysql 中测试了我的查询，它按预期工作。

我做错了什么？任何想法？谢谢

Answer 1

您正在为 SUM 值设置别名，因此您需要使用别名来映射它们。

$rsm->addScalarResult('SUM(r.quantity)', 'used');
...
$rsm->addScalarResult('(i.quantity - SUM(r.quantity))', 'balance');

更改为

$rsm->addScalarResult('used', 'used');
...
$rsm->addScalarResult('balance', 'balance');