Execute raw SQL using Doctrine 2

Question

I want to execute raw SQL using Doctrine 2  I need to truncate the database tables and initialize tables with default test data

User · Answer

Here s an example of a raw query in Doctrine 2 that I m doing   public function getAuthoritativeSportsRecords             sql              SELECT name                 event type                 sport type                 level           FROM vnn sport              em    this- gt getDoctrine  - gt getManager         stmt    em- gt getConnection  - gt prepare  sql        stmt- gt execute        return  stmt- gt fetchAll

User · Answer

In your model create the raw SQL statement  example below is an example of a date interval I had to use but substitute your own  If you are doing a SELECT add - fetchall   to the execute   call       sql    DELETE FROM tmp              WHERE lastedit   INTERVAL  5 minute   lt  NOW            stmt    this- gt getServiceLocator                    - gt get  Doctrine ORM EntityManager                    - gt getConnection                    - gt prepare  sql         stmt- gt execute

User · Answer

I found out the answer is probably      A NativeQuery lets you execute native   SQL  mapping the results according to   your specifications  Such a   specification that describes how an   SQL result set is mapped to a Doctrine   result is represented by a   ResultSetMapping    Source  Native SQL

User · Answer

How to execute a raw Query and return the data   Hook onto your manager and make a new connection    manager    this- gt getDoctrine  - gt getManager     conn    manager- gt getConnection      Create your query and fetchAll    result   conn- gt query  select foobar from mytable  - gt fetchAll      Get the data out of result like this    this- gt appendStringToFile  first row foobar is       result 0   foobar

User · Answer

I got it to work by doing this  assuming you are using PDO     Place query here  let s say you want all the users that have blue as their favorite color  sql    SELECT name FROM user WHERE favorite color    color      set parameters    you may set as many parameters as you have on your query  params  color     blue      create the prepared statement  by getting the doctrine connection  stmt    this- gt entityManager- gt getConnection  - gt prepare  sql    stmt- gt execute  params     I used FETCH COLUMN because I only needed one Column  return  stmt- gt fetchAll PDO  FETCH COLUMN     You can change the FETCH TYPE to suit your needs

User · Answer

sql - sql statement    em - entity manager   em- gt getConnection  - gt exec   sql

User · Answer

You can t  Doctrine 2 doesn t allow for raw queries  It may seem like you can but if you try something like this    sql    SELECT DATE FORMAT whatever createdAt    Y- m- d   FORM whatever       em    this- gt getDoctrine  - gt getManager     em- gt getConnection  - gt exec  sql     Doctrine will spit an error saying that DATE FORMAT is an unknown function   But my database  mysql  does know that function  so basically what is hapening is Doctrine is parsing that query behind the scenes  and behind your back  and finding an expression that it doesn t understand  considering the query to be invalid   So if like me you want to be able to simply send a string to the database and let it deal with it  and let the developer take full responsibility for security   forget it   Of course you could code an extension to allow that in some way or another  but you just as well off using mysqli to do it and leave Doctrine to it s ORM buisness

User · Answer

I had the same problem   You want to look the connection object supplied by the entity manager    conn    em- gt getConnection      You can then query execute directly against it    statement    conn- gt query  select foo from bar     num rows effected    conn- gt exec  update bar set foo 1      See the docs for the connection object at http   www doctrine-project org api dbal 2 0 doctrine dbal connection html

[php] Execute raw SQL using Doctrine 2

Examples related to php

Examples related to sql

Examples related to doctrine