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. In the last example we use the createNativeQuery() function of the Doctrine entity manager.

---

<?php

declare(strict_types=1);

namespace App\Controller\Snippet;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception;
use Doctrine\DBAL\ParameterType;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query\ResultSetMapping;

/**
 * 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
 * @property EntityManagerInterface $entityManager
 */
trait Snippet70Trait
{
    public function snippet70(): void
    {
        $sql = 'SELECT type, count(*) AS count FROM article WHERE id > ? GROUP BY type';
        echo '-- $statement->fetchAllAssociative() --'.PHP_EOL;
        echo $sql.PHP_EOL.PHP_EOL;

        try {
            $statement = $this->connection->prepare($sql);
        } catch (Exception $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);
        var_dump($statement->executeQuery()->fetchAllAssociative());

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

        // You can also use the createNativeQuery() function from the Doctrine entity manager
        echo '-- $this->entityManager->createNativeQuery() --'.PHP_EOL;
        $rsm = (new ResultSetMapping())
            ->addScalarResult('type', 'type')
            ->addScalarResult('count', 'count');
        var_dump($this->entityManager->createNativeQuery($sql2, $rsm)->getArrayResult());

        // 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

  Work with me!

<?php

declare(strict_types=1);

namespace App\Tests\Integration\Controller\Snippets;

use App\Tests\WebTestCase;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;

/**
 * @see Snippet70Trait
 */
final class Snippet70Test extends WebTestCase
{
    private Connection $connection;

    protected function setUp(): void
    {
        $this->connection = $this->getDoctrineDefaultConnection();
    }

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

        $rows2 = $this->connection->fetchAllAssociative('SELECT type, count(*) AS count FROM article GROUP BY type');
        self::assertIsArray($rows2);
        self::assertSame(['type', 'count'], array_keys($rows2[0] ?? []));
    }
}

---