Doctrine DQL dynamic ORDER BY parameter

Question

Im trying to pass the ORDER BY column as a parameter in DQL, like below:

$this->em->createQuery("SELECT t FROM Entities\Topic t ORDER BY :order")
     ->setParameters( array('order' => 't.name') )->getResult();

I guess it doesn't work because setParameter will escape :order, however the below solution doesn't seem very good:

$order = 't.name'; // Dynamic value
$this->em->createQuery("SELECT t FROM Entities\Topic t ORDER BY $order")
     ->getResult();

Is there a better way to solve this?

Answer 1

I solved this problem by adding the following method to the repository, also using the Query Builder:

class SomeEntityRepository extends ServiceEntityRepository
{
    private function addOrderBy(QueryBuilder $qb, string $field, string $dir, string $alias = 'g'): void
    {
        if (property_exists($this->getClassName(), $field)) {
            $dir = strtoupper($dir) === Criteria::DESC ? Criteria::DESC : Criteria::ASC;
            $qb->addOrderBy($alias . '.' . $field, $dir);
        }
    }
}

Answer 2

I know, this is an old question but until today this "problem" still exists. So, if you want to use dynamic ordering, you should use the doctrine queryBuilder.

I recently tested the current doctrine types on which one could be used for dynamic ordering. In my case I set the order column in the dql and only wanted to inject ASC/DESC.

Now, here is the test output:

DQL: SELECT w FROM App\Entity\Worker w ORDER BY w.id :order
Type "array" is invalid
Type "ascii_string" is invalid
Type "bigint" is invalid
Type "binary" is invalid
Type "blob" is invalid
Type "boolean" is invalid
Type "date" is invalid
Type "date_immutable" is invalid
Type "dateinterval" is invalid
Type "datetime" is invalid
Type "datetime_immutable" is invalid
Type "datetimetz" is invalid
Type "datetimetz_immutable" is invalid
Type "decimal" is invalid
Type "float" is invalid
Type "guid" is invalid
Type "integer" is invalid
Type "json" is invalid
Type "object" is invalid
Type "simple_array" is invalid
Type "smallint" is invalid
Type "string" is invalid
Type "text" is invalid
Type "time" is invalid
Type "time_immutable" is invalid

I'm using Symfony 5.3 and Doctrine 2.9

If someone is interested in the code, I used to test: I simply created a doctrine entity inside my symfony project. Then I created a test method inside the related repository object:

public function testOrder(string $order = 'ASC'): array
{
    $query = $this->getEntityManager()->createQuery(
        'SELECT w
        FROM App\Entity\Worker w
        ORDER BY w.id :order'
    );
    // map taken from: vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Type.php:101
    $doctrineTypeMap = [
        Types::ARRAY,
        Types::ASCII_STRING,
        Types::BIGINT,
        Types::BINARY,
        Types::BLOB,
        Types::BOOLEAN,
        Types::DATE_MUTABLE,
        Types::DATE_IMMUTABLE,
        Types::DATEINTERVAL,
        Types::DATETIME_MUTABLE,
        Types::DATETIME_IMMUTABLE,
        Types::DATETIMETZ_MUTABLE,
        Types::DATETIMETZ_IMMUTABLE,
        Types::DECIMAL,
        Types::FLOAT,
        Types::GUID,
        Types::INTEGER,
        Types::JSON,
        Types::OBJECT,
        Types::SIMPLE_ARRAY,
        Types::SMALLINT,
        Types::STRING,
        Types::TEXT,
        Types::TIME_MUTABLE,
        Types::TIME_IMMUTABLE,
    ];
    print_r('DQL: ' . $query->getDQL() . "<br/>");
    foreach ($doctrineTypeMap as $mappedType) {
        try {
            $query->setParameter('order', $order, $mappedType);
            print_r('Type "' . $mappedType . '" is ');
            $query->getSQL();
            print_r('valid' . "<br/>");
        } catch (\Exception $e) {
            print_r('invalid' . "<br/>");
        }
    }
    // Prevent symfony from rendering a view
    die();
}

Answer 3

In that case use Doctrines Querybuilder:

$order = 't.name'; // Dynamic value

$qb = $this->_em->createQueryBuilder();
$qb->select('t')
   ->from('Entities\Topic', 't')
   ->orderBy($order);