How to execute plain SQL using Doctrine in Symfony 3

DQL as a query language has SELECT, UPDATE and DELETE constructs that map to their corresponding SQL statement types.

However there are a lot of statements, like insert that are not allowed (or they don't exist) in DQL, because entities and their relations have to be introduced into the persistence context through EntityManager to ensure consistency of your object model.

Basically, if you want to use plain sql to insert information into your database you're throwing the Model in the garbage ... however, the feature of use plain sql comes in handy if you have really really complicated queries that can't be achieved using DQL for many reasons (difficult or there are no available doctrine extensions to use custom functions etc).

In this article you'll learn how to execute a plain SQL query using doctrine in Symfony 3 easily.

Usage

In order to execute a raw sql query, we need to get access to the database using the doctrine connection. You can retrieve the default connection from the entity manager.

The following example shows how to use a simple sql query from a Symfony controller :

<?php 

class LandingController extends Controller
{
    public function indexAction(Request $request)
    {
        $em = $this->getDoctrine()->getManager();

        $RAW_QUERY = 'SELECT * FROM my_table where my_table.field = 1 LIMIT 5;';
        
        $statement = $em->getConnection()->prepare($RAW_QUERY);
        $statement->execute();

        $result = $statement->fetchAll();
    }
}

The $result variable should contain an associative array with 5 rows of the database.

Binding parameters

In the same way the query builder of doctrine does, you can set parameters to your query using the bindValue method in your statement to make your queries more dynamical.

<?php 

class LandingController extends Controller
{
    public function indexAction(Request $request)
    {
        $em = $this->getDoctrine()->getManager();

        $RAW_QUERY = 'SELECT * FROM my_table where my_table.field = :status LIMIT 5;';
        
        $statement = $em->getConnection()->prepare($RAW_QUERY);
        // Set parameters 
        $statement->bindValue('status', 1);
        $statement->execute();

        $result = $statement->fetchAll();
    }
}

Multiple databases

As you're working with multiple databases, naturally you have more than 1 manager. As mentioned before, just get the connection of the entity manager that targets the database you want to query.

<?php 

class LandingController extends Controller
{
    public function indexAction(Request $request)
    {
        // Get connections
        $DatabaseEm1 = $this->getDoctrine()->getManager('database_or_connection_name1');
        $DatabaseEm2 = $this->getDoctrine()->getManager('database_or_connection_name2');

        // Write your raw SQL
        $rawQuery1 = 'SELECT * FROM my_table where my_table.field = :status LIMIT 10;';
        $rawQuery2 = 'SELECT * FROM my_table where my_table.field = :text LIMIT 5;';

        // Prepare the query from DATABASE1
        $statementDB1 = $DatabaseEm1->getConnection()->prepare($rawQuery1);
        $statementDB1->bindValue('status', 1);

        // Prepare the query from DATABASE2
        $statementDB2 = $DatabaseEm2->getConnection()->prepare($rawQuery2);
        $statementDB2->bindValue('text', 'Hello World');

        // Execute both queries
        $statementDB1->execute();
        $statementDB2->execute();

        // Get results :)
        $resultDatabase1 = $statementDB1->fetchAll();
        $resultDatabase2 = $statementDB2->fetchAll();
    }
}

Have fun !