Doctrine 2: execute raw sql?

New topics about Symfony 2 should go here

Moderators: tiagojsag, dcobalt

Doctrine 2: execute raw sql?

Postby denis_ » Sat Nov 12, 2011 7:59 pm

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.
denis_
Member
 
Posts: 47
Joined: Sun Sep 04, 2011 2:04 am

Re: Doctrine 2: execute raw sql?

Postby chris.zirkel » Sat Nov 12, 2011 8:12 pm

chris.zirkel
Member
 
Posts: 82
Joined: Fri Jul 22, 2011 2:51 am

Re: Doctrine 2: execute raw sql?

Postby denis_ » Sun Nov 13, 2011 4:30 pm

What about direct SQL queries? Native queries isn't really what I was looking for. I don't want to map my results.
denis_
Member
 
Posts: 47
Joined: Sun Sep 04, 2011 2:04 am

Re: Doctrine 2: execute raw sql?

Postby althaus » Mon Nov 14, 2011 4:39 pm

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
althaus
Faithful Member
 
Posts: 605
Joined: Tue Oct 18, 2011 2:20 pm
Location: Germany

Re: Doctrine 2: execute raw sql?

Postby dcobalt » Mon Nov 14, 2011 4:54 pm

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();
dcobalt
Faithful Member
 
Posts: 283
Joined: Thu Oct 20, 2011 6:06 pm

Re: Doctrine 2: execute raw sql?

Postby denis_ » Tue Nov 15, 2011 3:56 am

althaus wrote: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.
denis_
Member
 
Posts: 47
Joined: Sun Sep 04, 2011 2:04 am

Re: Doctrine 2: execute raw sql?

Postby althaus » Tue Nov 15, 2011 8:11 pm

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. :mrgreen:

Perhaps I'll setup a blog to collect a lot of common use cases.
althaus
Faithful Member
 
Posts: 605
Joined: Tue Oct 18, 2011 2:20 pm
Location: Germany

Re: Doctrine 2: execute raw sql?

Postby dreipunktnull » Wed Nov 16, 2011 10:02 am

althaus wrote: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.).
User avatar
dreipunktnull
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany

Re: Doctrine 2: execute raw sql?

Postby althaus » Wed Nov 16, 2011 2:47 pm

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
althaus
Faithful Member
 
Posts: 605
Joined: Tue Oct 18, 2011 2:20 pm
Location: Germany


Return to General Symfony 2 discussion

Who is online

Users browsing this forum: No registered users and 10 guests