ucscode/doctrine-expression

A PHP class for handling SQL queries across different database platforms using Doctrine.

v1.0.0 2024-10-27 01:09 UTC

Requires

• doctrine/dbal: ^3
• doctrine/orm: ^3.2

Requires (Dev)

• friendsofphp/php-cs-fixer: ^3.0
• phpunit/phpunit: ^9.5

MIT

README

DoctrineExpression is a PHP library designed to facilitate cross-database compatibility when constructing SQL queries using Doctrine ORM. By abstracting database-specific syntax, developers can write more maintainable code that seamlessly adapts to different database drivers.

Table of Contents

• Features
• Requirements
• Installation
• Usage
• License
• Contributing
• Acknowledgments

Features

• Write database-agnostic queries that adapt to MySQL, PostgreSQL, SQLite, and more.
• Define SQL queries for each supported database platform using simple callable functions.
• Easily integrates into your existing Doctrine setup.

Requirements

• PHP 8.2 or higher
• Composer

Installation

To install DoctrineExpression, run the following command in your terminal:

composer require ucscode/doctrine-expression

Usage

Scenario: Changing Database Drivers

Consider a scenario where your application is initially configured to use MySQL. The following configuration might be present in your Symfony .env file:

DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app"

You might have a query that leverages JSON capabilities specific to MySQL:

$queryBuilder = $entityManager->createQueryBuilder();

$result = $queryBuilder->select('u')
    ->from('App\Entity\User', 'u')
    ->where('JSON_CONTAINS(u.roles, :roles) = 1')
    ->setParameter('roles', json_encode('ROLE_USER'))
    ->getQuery()
    ->getResult();

This query works well with MySQL. However, if you decide to switch to PostgreSQL, you might change your .env configuration to:

DATABASE_URL="postgresql://app:!ChangeMe!@127.0.0.1:5432/app"

Adapting the Query for PostgreSQL

Switching to PostgreSQL requires some modifications due to syntax differences. For example, PostgreSQL handles JSON operations differently:

$queryBuilder = $entityManager->createQueryBuilder();

$result = $queryBuilder->select('u')
    ->from('App\Entity\User', 'u')
    ->where('u.roles @> :role')
    ->setParameter('role', json_encode(['ROLE_USER']))
    ->getQuery()
    ->getResult();

Leveraging DoctrineExpression

Instead of rewriting queries for each database driver, you can utilize DoctrineExpression to manage this complexity efficiently.

Example Usage:

use Ucscode\DoctrineExpression\DoctrineExpression;
use Ucscode\DoctrineExpression\DriverEnum;

// Initialize the DoctrineExpression with the EntityManager
$expression = new DoctrineExpression($entityManager);

// Define the MySQL query
$expression->defineQuery(DriverEnum::PDO_MYSQL, function($entityManager) {
    return $entityManager->createQueryBuilder()
        ->select('u')
        ->from('App\Entity\User', 'u')
        ->where('JSON_CONTAINS(u.roles, :roles) = 1');
});

// Define the PostgreSQL query
$expression->defineQuery(DriverEnum::PDO_PGSQL, function($entityManager) {
    return $entityManager->createQueryBuilder();
        ->select('u')
        ->from('App\Entity\User', 'u')
        ->where('u.roles @> :roles');
});

// Fetch the compatible query builder based on the current database platform
$result = $expression->getCompatibleResult()
    ->setParameter('roles', json_encode(['ROLE_USER']))
    ->getQuery()
    ->getResult();

The getCompatibleResult() method checks which platform is active (e.g., MySQL or PostgreSQL) and selects the corresponding query that was previously defined using defineQuery().

@ V1.1

For drivers that might use the same syntax, the getDefinedQuery() can be used to eliminate the need to write repetitive patterns.

$expression->defineQuery(DriverEnum::PDO_SQLITE, function($em, $self) {
    return $self->getDefinedQuery(DriverEnum::PDO_MYSQL);
})

Conclusion

By using DoctrineExpression, you can maintain a clean and consistent codebase while easily adapting to changes in database platforms. This library not only saves time but also enhances collaboration among developers familiar with different SQL dialects.

Helpful Snippet for Common Use Case

How to find users by one or more roles in Symfony using DoctrineExpression

class UserRepository ... {
    ...
    public function findUsersByRoles(string|array $roles): array
    {
        // Ensure that $roles is an array containing unique values
        $roles = array_unique(array_values(is_array($roles) ? $roles : [$roles]));

        // Create an doctrine expression instance
        $expression = new DoctrineExpression($this->getEntityManager());

        // When using MySQL
        $expression->defineQuery(DriverEnum::PDO_MYSQL, function () use ($roles): array {
            $condition = implode(' OR ', array_map(function (int $key, string $value) {
                return sprintf('entity.roles LIKE :%s%d', $value, $key);
            }, array_keys($roles), $roles));

            $builder = $this->createQueryBuilder('entity')->where($condition);

            foreach ($roles as $key => $role) {
                $builder->setParameter(sprintf('%s%d', $role, $key), str_replace(':role', $role, '%":role"%'));
            }

            return $builder->getQuery()->getResult();
        });

        // When using PostgreSQL
        $expression->defineQuery(DriverEnum::PDO_PGSQL, function () use ($roles): array {
            // Get the table name from the entity's metadata (no hard coding)
            $tableName = $this->getEntityManager()->getClassMetadata(User::class)->getTableName();

            $sql = <<<SQL
                SELECT "$tableName".id
                FROM "$tableName"
                WHERE %s
            SQL;

            $condition = implode(' OR ', array_map(function(string $value) use ($tableName) {
                return sprintf('"%s".roles::jsonb @> \'%s\'::jsonb', $tableName, json_encode([$value]));
            }, $roles));
            
            $nativeSQL = sprintf($sql, $condition);
            $result = $this->getEntityManager()->getConnection()->executeQuery($nativeSQL);

            return $this->findBy(['id' => array_map(fn (array $user) => $user['id'], $result->fetchAllAssociative())]);
        });

        return $expression->getCompatibleResult();
    }

}

License

This project is licensed under the MIT License - see the LICENSE file for details.

Contributing

Contributions are welcome!

Acknowledgments

• Doctrine ORM for providing a powerful and flexible object-relational mapping layer for PHP.