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.

Answer 1

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->getDoctrine()->getManager();
    $stmt = $em->getConnection()->prepare($sql);
    $stmt->execute();
    return $stmt->fetchAll();
}   

Answer 2

//$sql - sql statement
//$em - entity manager

$em->getConnection()->exec( $sql );

Answer 3

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->entityManager->getConnection()->prepare($sql);
$stmt->execute($params);
//I used FETCH_COLUMN because I only needed one Column.
return $stmt->fetchAll(PDO::FETCH_COLUMN);

You can change the FETCH_TYPE to suit your needs.

Answer 4

How to execute a raw Query and return the data.

Hook onto your manager and make a new connection:

$manager = $this->getDoctrine()->getManager();
$conn = $manager->getConnection();

Create your query and fetchAll:

$result= $conn->query('select foobar from mytable')->fetchAll();

Get the data out of result like this:

$this->appendStringToFile("first row foobar is: " . $result[0]['foobar']);

Answer 5

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.

Answer 6

I had the same problem. You want to look the connection object supplied by the entity manager:

$conn = $em->getConnection();

You can then query/execute directly against it:

$statement = $conn->query('select foo from bar');
$num_rows_effected = $conn->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

Answer 7

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' < NOW() ";

    $stmt = $this->getServiceLocator()
                 ->get('Doctrine\ORM\EntityManager')
                 ->getConnection()
                 ->prepare($sql);

    $stmt->execute();

Answer 8

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->getDoctrine()->getManager();
$em->getConnection()->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.