PHP code example of ucscode / doctrine-expression

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

ucscode / doctrine-expression example snippets

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

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

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();

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
});

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

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 = <<<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()
            )
        ]);
    }
}