Running raw SQL queries with Doctrine

Published on 2020-01-16 • Modified on 2020-01-16

In this snippet we will see how to run raw SQL queries via the Doctrine DBAL connection instance. In the first example, we prepare a statement and we bind an integer parameter to the query. In the second example, as there is no parameter to replace, we can directly use the fetchAll shortcut function to retrieve data.


<?php declare(strict_types=1);

// src/Controller/Snippet/Snippet70Trait.php

namespace App\Controller\Snippet;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\ParameterType;
use Symfony\Component\Config\Definition\Exception\InvalidTypeException;

/**
 * I am using a PHP trait to isolate each snippet in a file.
 * This code should be called from a Symfony controller extending AbstractController (as of Symfony 4.2)
 * or Symfony\Bundle\FrameworkBundle\Controller\Controller (Symfony <= 4.1).
 * Services are injected in the main controller constructor.
 *
 * @property Connection $connection
 */
trait Snippet70Trait
{
    public function snippet70(): void
    {
        if (!$this->connection instanceof Connection) {
            throw new InvalidTypeException('Doctrine connection is invalid.');
        }

        $sql = 'SELECT type_id, count(*) AS count FROM article WHERE id > ? GROUP BY type_id';
        echo $sql.PHP_EOL.PHP_EOL;

        try {
            $statement = $this->connection->prepare($sql);
        } catch (DBALException $e) {
            throw new \RuntimeException(sprintf('Error while preparing the SQL: %s', $e->getMessage()));
        }

        // Properly replace first placeholder "?" in the SQL by an integer value
        $statement->bindValue(1, 20, ParameterType::INTEGER);
        $statement->execute();
        var_dump($statement->fetchAll()) ;

        $sql2 = 'SELECT type_id, count(*) AS count FROM article GROUP BY type_id';
        echo $sql2.PHP_EOL.PHP_EOL;
        var_dump($this->connection->fetchAll($sql2));

        // That's it! 😁
    }
}

 Run this snippet  ≪ this.showUnitTest ? this.trans.hide_unit_test : this.trans.show_unit_test ≫  More on Stackoverflow   Read the doc   Read the doc

<?php declare(strict_types=1);

namespace App\Tests\Controller\Snippets;

use Doctrine\DBAL\ParameterType;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;

/**
 * @covers Snippet70Trait
 */
final class Snippet70Test extends KernelTestCase
{
    private $connection;

    protected function setUp(): void
    {
        self::bootKernel();
        $this->connection = self::$kernel->getContainer()->get('doctrine.dbal.default_connection');
    }

    /**
     * @covers Snippet70Trait::snippet70
     */
    public function testSnippet70(): void
    {
        $statement = $this->connection->prepare('SELECT type_id, count(*) AS count FROM article WHERE id > ? GROUP BY type_id');
        $statement->bindValue(1, 20,  ParameterType::INTEGER);
        $statement->execute();
        $res = $statement->fetchAll();
        $this->assertIsArray($res);
        $this->assertSame(['type_id', 'count'], array_keys($res[0] ?? []));

        $res2 = $this->connection->fetchAll('SELECT type_id, count(*) AS count FROM article GROUP BY type_id');
        $this->assertIsArray($res2);
        $this->assertSame(['type_id', 'count'], array_keys($res2[0] ?? []));
    }
}