douzhu5900
2017-08-05 14:04
浏览 43
已采纳

使用Doctrine QueryBuilder时变量未定义

I want to make alternative way for making stored procedures by using Doctrine but I am stuck, could any one help me?

Example stored procedure to be formed:

CREATE PROCEDURE catalog_get_department_details(IN DepartmentName)
    BEGIN
    SELECT name, description
    FROM
    department
    WHERE name = name;

Departments Entity:

/**
 * @ORM\Entity(repositoryClass="AppBundle\Repository\departmentsRepository")
 * @ORM\Table(name="departments")
 */
class departments
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer")
     */
    private $department_id;

    /**
     * @ORM\Column(type="string")
     */
    private $name;

    /**
     * @ORM\Column(type="string", nullable=true)
     */
    private $description;

    /**
     * @ORM\OneToMany(targetEntity="categories",mappedBy="departments")
     */
    private $categories;

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

    public function getDepartmentId()
    {
        return $this->department_id;
    }

    public function setDepartmentId($department_id)
    {
        $this->department_id = $department_id;
    }

    /**
     * @return mixed
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param mixed $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return mixed
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * @param mixed $description
     */
    public function setDescription($description)
    {
        $this->description = $description;
    }

The scenario is when the route is /index/departmentname/Regional ; my DefaultController will capture Regional as parameter

DefaultController:

class DefaultController extends Controller
{
    /**
     * @Route ("/index/department/{department_name}")
     */
    function departmentAction($department_name)
    {
        // accessing departmentsRepository 
        $categoriesRepository = $this->getDoctrine()->getManager()
            ->getRepository('AppBundle:departments');
        $categoriesRepository->getDepartmentDetails($department_name);
    }

departmentsRepository:

class departmentsRepository extends \Doctrine\ORM\EntityRepository
{
    function getDepartmentDetails($departmentName)
    {
        $em=$this->getEntityManager()->getRepository('AppBundle:departments');
        $qb=$em->createQueryBuilder('dep');
        $qb->select('dep.name','dep.description');
        $qb->where("dep.name=$departmentName");

When I call var_dump($qb->getDQL());die; it shows me exactly what I want:

SELECT dep.name, dep.description FROM AppBundle\Entity\departments dep WHERE dep.name=Regional

I then execute it by calling

$qb->getQuery()->execute();

But I receive the following error:

[Semantical Error] line 0, col 86 near 'Regional': Error: 'Regional' is not defined.

Any idea what I'm doing wrong?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanmen8491 2017-08-05 14:21
    已采纳

    Your dep.name value isn't being escaped. You would expect the query to look like this instead:

    WHERE dep.name='Regional'
    

    But what you should be doing, and what is safer, is binding that to a parameter, like so:

    $em = $this->getEntityManager()->getRepository('AppBundle:departments');
    $qb = $em->createQueryBuilder('dep');
    $qb->select('dep.name', 'dep.description');
    $qb->where("dep.name = :departmentName");
    $qb->setParameter('departmentName', $departmentName);
    

    Doctrine will handle the escaping for you, and safely. This also allows you to avoid SQL injection attacks. Also since you are already in your departments repository you should be able to use the _em value as a shortcut, and also not have to re-specify the departments entity, like so:

    $qb = $this->_em->createQueryBuilder('dep');
    $qb->select('dep.name', 'dep.description');
    $qb->where("dep.name = :departmentName");
    $qb->setParameter('departmentName', $departmentName);
    

    Side not, in your controller action you are calling the repository function but not actually saving the results to any variable.

    打赏 评论

相关推荐 更多相似问题