PHPackages                             ucscode/doctrine-expression - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. ucscode/doctrine-expression

ActiveLibrary

ucscode/doctrine-expression
===========================

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

v2.0.2(3mo ago)076↓100%MITPHPPHP >=8.1CI passing

Since Oct 27Pushed 3mo ago1 watchersCompare

[ Source](https://github.com/ucscode/doctrine-expression)[ Packagist](https://packagist.org/packages/ucscode/doctrine-expression)[ RSS](/packages/ucscode-doctrine-expression/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependencies (4)Versions (8)Used By (0)

DoctrineExpression
==================

[](#doctrineexpression)

[![](./assets/design.webp)](./assets/design.webp)

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

[](#table-of-contents)

- [Features](#features)
- [Requirements](#requirements)
- [Installation](#installation)
- [Usage](#usage)
- [License](#license)
- [Contributing](#contributing)
- [Acknowledgments](#acknowledgments)

Features
--------

[](#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
------------

[](#requirements)

- PHP 8.2 or higher
- Composer

Installation
------------

[](#installation)

To install `Doctrine\Expression`, run the following command in your terminal:

```
composer require ucscode/doctrine-expression
```

Usage
-----

[](#usage)

### Scenario: Changing Database Drivers

[](#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:

```
$result = $queryBuilder
    ->where('JSON_CONTAINS(u.roles, :roles) = 1')
    ->setParameter('roles', json_encode('ROLE_USER'))
;
```

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

[](#adapting-the-query-for-postgresql)

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

```
$result = $queryBuilder
    ->where('u.roles @> :role')
    ->setParameter('role', json_encode(['ROLE_USER']))
;
```

### Leveraging DoctrineExpression

[](#leveraging-doctrineexpression)

Instead of rewriting queries for each database driver, you can utilize `Doctrine\Expression` to manage this complexity efficiently.

#### Example Usage:

[](#example-usage)

```
use Ucscode\Doctrine\Expression\Expression;
use Ucscode\Doctrine\Expression\DriverEnum;

// Initialize the Doctrine\Expression with the EntityManager
$expression = new Expression($entityManager);

// Define the MySQL query
$expression->defineQuery(DriverEnum::PDO_MYSQL, function(Expression $expr) {
    return $expr->getEntityManager()
        ->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(Expression $expr) {
    return $expr->getEntityManger()
        ->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()`.

### Providing extra Parameters to the query

[](#providing-extra-parameters-to-the-query)

You might want a set of data to be available when defining your query in a closure. For that, you can pass an array with the parameter as second argument of the `Doctrine\Expression` or you can call the `Doctrine\Expression::set()` method

#### Example:

[](#example)

```
use Ucscode\Doctrine\Expression\Expression;
use Ucscode\Doctrine\Expression\DriverEnum;

// setting with __construct() argument
$expression = new Expression($entityManager, [
    'amount' => 30000,
    'roles' => ['ROLE_ADMIN', 'ROLE_USER'],
    'entity' => new stdClass(),
])

// using the setter
$expression->set('dev.name', 'Ucscode');

$expression->defineQuery(DriverEnum::PDO_PGSQL, function(Expression $expr) {
    $expr->get('dev.name'); // Ucscode
    $expr->get('entity'); // stdClass instance
});
```

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

[](#conclusion)

By using `Doctrine\Expression`, 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

[](#helpful-snippet-for-common-use-case)

How to find users by one or more roles in Symfony using `Doctrine\Expression`

##### Note:

[](#note)

You can handle PostgreSQL JSON/JSONB using libraries like one of the following:

-
-

The snippet below does not employ any external libraries and works without doctrine extension dependencies but only for this simple case.

```
class UserRepository extends ServiceEntityRepository
{
    /**
     * Find all users matching any of the provided role(s)
     *
     * @param string|array $roles
     * @return array
     */
    public function findByRoles(string|array $roles): array
    {
        $expression = new Expression($this->getEntityManager(), [
            'roles' => array_unique(array_values(
                is_array($roles) ? $roles : [$roles]
            )),
        ]);

        $expression
            ->defineQuery(DriverEnum::PDO_MYSQL, fn ($expr) => $this->mysqlExpression($expr)) // When using MySQL
            ->defineQuery(DriverEnum::PDO_PGSQL, fn ($expr) => $this->pgsqlExpression($expr)) // When using PostgreSQL
        ;

        return $expression->getCompatibleResult();
    }

    /**
     * Expression used if database engine is MYSQLI
     *
     * @param array $roles
     * @return array
     */
    private function mysqlExpression(Expression $expr): array
    {
        /** @var array $roles */
        $roles = $expr->get('roles');

        $condition = implode(' OR ', array_map(
            fn (int $key, string $value) => 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();
    }

    /**
     * Expression used if database engine is PostgreSQL
     *
     * @param array $roles
     * @return array
     */
    private function pgsqlExpression(Expression $expr): array
    {
        /** @var array $roles */
        $roles = $expr->get('roles');

        // Get the table name from the entity's metadata
        $tableName = $this->getEntityManager()->getClassMetadata(User::class)->getTableName();

        $sql = getEntityManager()->getConnection()->executeQuery($nativeSQL);

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

---

License
-------

[](#license)

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

Contributing
------------

[](#contributing)

Contributions are welcome! Fork and send a pull request

Acknowledgments
---------------

[](#acknowledgments)

- [Doctrine ORM](https://github.com/doctrine/orm/) for providing a powerful and flexible object-relational mapping layer for PHP.

###  Health Score

40

—

FairBetter than 88% of packages

Maintenance81

Actively maintained with recent releases

Popularity10

Limited adoption so far

Community7

Small or concentrated contributor base

Maturity52

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~92 days

Total

6

Last Release

100d ago

Major Versions

1.1.0 → 2.0.02025-02-02

### Community

Maintainers

![](https://www.gravatar.com/avatar/65673b1b31e87471999a7614d107e7e061a38bf72191d149c66c1b943124e09c?d=identicon)[ucscode](/maintainers/ucscode)

---

Top Contributors

[![ucscode](https://avatars.githubusercontent.com/u/34024404?v=4)](https://github.com/ucscode "ucscode (13 commits)")

###  Code Quality

TestsPHPUnit

Code StylePHP CS Fixer

### Embed Badge

![Health badge](/badges/ucscode-doctrine-expression/health.svg)

```
[![Health](https://phpackages.com/badges/ucscode-doctrine-expression/health.svg)](https://phpackages.com/packages/ucscode-doctrine-expression)
```

###  Alternatives

[sylius/sylius

E-Commerce platform for PHP, based on Symfony framework.

8.4k5.6M650](/packages/sylius-sylius)[scienta/doctrine-json-functions

A set of extensions to Doctrine that add support for json query functions.

58523.9M35](/packages/scienta-doctrine-json-functions)[sulu/sulu

Core framework that implements the functionality of the Sulu content management system

1.3k1.3M152](/packages/sulu-sulu)[damienharper/auditor-bundle

Integrate auditor library in your Symfony projects.

4542.8M](/packages/damienharper-auditor-bundle)[sonata-project/entity-audit-bundle

Audit for Doctrine Entities

644989.8k1](/packages/sonata-project-entity-audit-bundle)[contao/core-bundle

Contao Open Source CMS

1231.6M2.3k](/packages/contao-core-bundle)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)