[DDC-1824] Native Query fail Created: 17/May/12  Updated: 27/May/12  Resolved: 27/May/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.1.6
Fix Version/s: None
Security Level: All

Type: Bug Priority: Blocker
Reporter: Moritz Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None
Environment:

XAMPP on Windows 7
Doctrine in Combination with Symfony



 Description   

I keep getting an error while I'm trying to perfom an native query.
If there is no result then everything is fine and I don't get any errors.

Notice: Undefined index: id in C:\xampp\htdocs\Symfony\vendor\doctrine\lib\Doctrine\ORM\UnitOfWork.php line 1949

 
$sql = "SELECT q.question FROM question q WHERE MATCH (q.question) AGAINST (?)";

$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
$rsm->addEntityResult('WWMGameBundle:Question', 'q');
$rsm->addFieldResult('q', 'question', 'question');

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $request->request->get('q'));

$search = $query->getResult();
 
/**
* WWM\Entity\Question
*
* @ORM\Entity(repositoryClass="WWM\Entity\QuestionsRepository")
* @ORM\Table(name="question")
*/
class Question
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var integer $level
     *
     * @ORM\Column(name="level", type="integer", nullable=false)
     */
    private $level;

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



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

    /**
     * Set level
     *
     * @param integer $level
     */
    public function setLevel($level)
    {
        $this->level = $level;
    }

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

    /**
     * Set question
     *
     * @param string $question
     */
    public function setQuestion($question)
    {
        $this->question = $question;
    }

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


 Comments   
Comment by Benjamin Eberlei [ 17/May/12 ]

You have to add the id as field aswell using $rsm->addFieldResult()

Comment by Moritz [ 17/May/12 ]

It doesn't matter if I use the id as well. I keep getting the same error message:

$sql = "SELECT q.question FROM question q WHERE MATCH (q.question) AGAINST (?)";

$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
$rsm->addEntityResult('WWMGameBundle:Question', 'q');
$rsm->addFieldResult('q', 'question', 'question');
$rsm->addFieldResult('q', 'id', 'id');

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $request->request->get('q'));

$search = $query->getResult();
Comment by Moritz [ 20/May/12 ]

It doesn't matter if I use the id as well. I keep getting the same error message.

Comment by Benjamin Eberlei [ 27/May/12 ]

Do you want to fetch the whole Question Entity? Or just the question as string? Since you only do q.question i suppose using a scalar result instead of the entity works.

$sql = "SELECT q.question FROM question q WHERE MATCH (q.question) AGAINST (?)";

$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
$rsm->addScalarResult('question', 'question');

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $request->request->get('q'));

$search = $query->getResult();

However if you want the whole entity, then you have to fetch the ID from sql as well:

$sql = "SELECT q.id, q.question FROM question q WHERE MATCH (q.question) AGAINST (?)";

$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
$rsm->addEntityResult('WWMGameBundle:Question', 'q');
$rsm->addFieldResult('q', 'question', 'question');
$rsm->addFieldResult('q', 'id', 'id');

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $request->request->get('q'));

$search = $query->getResult();
Comment by Moritz [ 27/May/12 ]

Now it works perfectly fine. Thank you

Generated at Fri Oct 31 23:00:50 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.