Execute raw SQL using Doctrine 2

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();
}   

Most of the answers here are now deprecated since Doctrine DBAL 2.13. For example, execute is deprecated and fetchAll will be removed in 2022.

/**
 * BC layer for a wide-spread use-case of old DBAL APIs
 *
 * @deprecated This API is deprecated and will be removed after 2022
 *
 * @return list<mixed>
 */
public function fetchAll(int $mode = FetchMode::ASSOCIATIVE): array

It's no longer recommended to use execute and then fetchAll since both are deprecated.

* @deprecated Statement::execute() is deprecated, use Statement::executeQuery() or executeStatement() instead

* @deprecated Result::fetchAll is deprecated, and will be removed after 2022

So we have to be more specific when executing raw SQL as well as fetching result.

Instead of using Statement::execute(), we need to use executeQuery or executeStatement.

executeQuery return object Result:

Executes the statement with the currently bound parameters and return result.

executeStatement return int:

Executes the statement with the currently bound parameters and return affected rows.

Instead of using Result::fetchAll(), we need to use fetchAllNumeric or fetchAllAssociative (and more).

To get a simple result, you would have to do:

public function getSqlResult(EntityManagerInterface $em)
{   
    $sql = " 
        SELECT firstName,
               lastName
          FROM app_user
    ";

    $stmt = $em->getConnection()->prepare($sql);
    $result = $stmt->executeQuery()->fetchAllAssociative();
    return $result;
}   

And with parameters:

public function getSqlResult(EntityManagerInterface $em)
{   
    $sql = " 
        SELECT firstName,
               lastName,
               age
          FROM app_user
          where age >= :age
    ";

    $stmt = $em->getConnection()->prepare($sql);
    $stmt->bindParam('age', 18);
    $result = $stmt->executeQuery()->fetchAllAssociative();
    return $result;
}   

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

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 happening 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 its ORM business.