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.

Minda

By the way, when I want to do automated database gruntwork, like doing mysqldumps or loading in data from previous dumps or dropping tables, I usually write a shell script for that work and then write a task (or "command", in Symfony2 language) that executes the shell script. The purpose of an ORM, as I understand it, is to abstract away repetitive work, and if you're doing something like truncating a table, I don't see how it would make sense to bring Doctrine into the picture since Doctrine doesn't make that task any easier. – Delogu

Answer 1

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.

Minda

This is the accepted answer but I still don't see how this part of Doctrine is useful because you always need the ResultSetMapping. I don't want it to map the results to Entities .... that defaults the point of running arbitrary SQL! – Bruyn

@Bruyn I found this post helpful for running raw queries in Doctrine 2: forum.symfony-project.org/viewtopic.php?f=23&t=37872 – Delogu

Also Non-native Native SQL will not execute every possible SQL query. DELETE/UPDATE/INSERT wont work, nor some table definitions that do not follow doctrine assumptions. (M2M joining table without ids). So this answer is not universal. Nor should be accepted as INSERTs wont work. – Malebranche

The accepted answer has an invalid link now. – Skyros

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

Delogu

Nice answer. To get entity manager in this code, you may use $this->getDoctrine()->getManager() in place of this code above "$this->getEntityManager()", this way it worked for me straight away. – Philanthropy

hey its giving me Call to undefined method Index::getDoctrine() what should i do – Element

i m using codeigniter with doctrine 2 wildlyinaccurate.com/integrating-doctrine-2-with-codeigniter-2 – Element

This led me in the right direction but wasn't exactly what I needed. I suspect that the age of the answer makes a difference. I used: ...getConnection()->query($sql); and didn't have to run $stmt->execute(); – Alyworth

Note that with Symfony4 and autowiring, you can can type hint EntityManagerInterface $entityManager and then call $entityManager->getConnection() – Sosanna

DEPRECATED: execute and fetchAll are deprecated in Doctrine DBAL >2.13, see my answer for more information. – Cellobiose

Answer 3

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

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

Thane

Also a good idea to call prepare() instead of exec so you can still get prepared statement support. – Fainthearted

$em->getConnection()->executeQuery($sql) now :-) – Reahard

DEPRECATED: exec, execute and fetchAll are deprecated in Doctrine DBAL >2.13, see my answer for more information. – Cellobiose

Answer 4

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.

Capias

Best example out of all of them – Normie

DEPRECATED: execute and fetchAll are deprecated in Doctrine DBAL >2.13, see my answer for more information. – Cellobiose

Answer 5

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

Cellobiose

Answer 6

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']);

Batten

query() is for when the SQL returns some data you want to use; exec() is for when it doesn't – Curson

DEPRECATED: exec, execute and fetchAll are deprecated in Doctrine DBAL >2.13, see my answer for more information. – Cellobiose

Answer 7

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

Code

Answer 8

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

Blueing

Answer 9

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.

Sibyl

Answer 10

I am using 2.15.4 of doctrine/orm, and assuming that $entityManager in your code is an instance of Doctrine\ORM\EntityManagerInterface.

There are two cases when you want to execute raw SQL queries:

1. The query result does not matter and can be ignored

This seems to match the original question, and the following snippet can be used:

$sqls = [
    'DROP TABLE IF EXISTS _performance_test',
    'CREATE TABLE _performance_test (counter BIGINT UNSIGNED NOT NULL)',
    'INSERT INTO _performance_test (counter) VALUES (0)',
];
$connection = $entityManager->getConnection();
foreach ($sqls as $sql) {
    $connection->executeStatement($sql);
}

2. The query result matters and is used

$sql = 'SELECT counter FROM _performance_test LIMIT 1';
$result = $entityManager->getConnection()->prepare($sql)->executeQuery();

In this case $result is an instance of Doctrine\DBAL\Result, and you can call any of its methods (fetchAssociative, fetchOne, fetchAllAssociative, etc). Example:

$counter = $result->fetchOne();

Colpin