Skip to content

Loading…

DDC-1824: Native Query fail #2482

Closed
doctrinebot opened this Issue · 6 comments

2 participants

@doctrinebot

Jira issue originally created by user mowee:

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;
    }
}
@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by mowee:

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();
@doctrinebot

Comment created by mowee:

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

@doctrinebot

Comment created by @beberlei:

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();
@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot

Comment created by mowee:

Now it works perfectly fine. Thank you :)

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.