Doctrine 2: execute raw sql?

[denis_]

Hi, I have some complex MySQL queries which would be hard to turn into DQL.

Is it possible to run raw sql queries using Doctrine 2? If so, how?

I googled alot about this issue and could only find the solution for Doctrine 1.2.

[chris.zirkel]

Maybe this: http://www.doctrine-project.org/docs/or ... e-sql.html

[denis_]

What about direct SQL queries? Native queries isn't really what I was looking for. I don't want to map my results.

[althaus]

You can do simple stuff like this:

Code: Select all

    public function foobar($foobar)
    {
      $stmt = $this->getEntityManager()
                   ->getConnection()
                   ->prepare('SELECT COUNT(id) AS num, foo FROM bar WHERE foobar = :foobar GROUP BY foo');
      $stmt->bindValue('foobar ', $foobar);
      $stmt->execute();
      return $stmt->fetchAll();
    }


This is some code I use in a custom EntityRepository, but you can do it anywhere you get access to the EM.

Cheers
Matthias

[dcobalt]

Something like this is also doable - maybe a bit more complicated, but I like it better (notice how, via mapping, I can get "answers" as "count" and "optionname" as "option", which wouldn't work in the query itself as they're reserved words):

Code: Select all

$rsm = new ResultSetMapping;
        $rsm->addScalarResult('optionname', 'option');
        $rsm->addScalarResult('answers', 'count');

        return $this->_em->createNativeQuery('
       SELECT
         qo.title as optionname,
         COUNT(qap.answer_id) as answers
      FROM
         quiz_answer_option qap
         INNER JOIN quizoption qo ON qo.id = qap.option_id
         INNER JOIN quizquestion qq ON qq.id = qo.quizquestion_id
      WHERE
         qq.active
         AND qq.id = :quizquestionid
      GROUP BY qap.option_id
      ORDER BY qo.number asc
        ', $rsm)
                        ->setParameter('quizquestionid', $quizquestion->getId())
                        ->getResult();

[denis_]

You can do simple stuff like this:

Code: Select all

    public function foobar($foobar)
    {
      $stmt = $this->getEntityManager()
                   ->getConnection()
                   ->prepare('SELECT COUNT(id) AS num, foo FROM bar WHERE foobar = :foobar GROUP BY foo');
      $stmt->bindValue('foobar ', $foobar);
      $stmt->execute();
      return $stmt->fetchAll();
    }


This is some code I use in a custom EntityRepository, but you can do it anywhere you get access to the EM.

Cheers
Matthias

You saved my day. I googled this for hours and I couldn't figure out how to run direct sql queries via DC2.

[althaus]

Yeah... gathering the docs you need can be a pain in the ass at the moment. Hopefully the Symfony2/Doctrine2 documention will evolve in the next months.

Perhaps I'll setup a blog to collect a lot of common use cases.

[dreipunktnull]

Perhaps I'll setup a blog to collect a lot of common use cases.

Good idea! Some collection of Symfony snippets. If I can be of any help let me know (hosting i.e.).

[althaus]

I thought about just using Posterous or a similar service to reduce maintenace to a minimum. Otherwise I can use my webserver for it.

At least already bought a domain for it. xD