Back to Blog
Jan 23rd, 2024

Doctrine's HIDDEN gem

weaverryan avatar Leannapelham avatar

Written by weaverryan, and Leannapelham

Doctrine's HIDDEN gem

We had a great question from a user recently where the answer involved a little-known Doctrine feature called HIDDEN. You won't need it often, but it deserves its 5 in the spotlight!

HIDDEN allows you to select a field so that you can sort by it... but without include it in the results:

/** @return Voyage[] */
public function findAllOrderedByPastFirst(): array
    return $this->createQueryBuilder('voyage')
        ->addSelect('CASE WHEN voyage.leaveAt < :now THEN 1 ELSE 0 END AS HIDDEN isPast')
        ->orderBy('isPast', 'DESC')
        ->setParameter('now', new \DateTimeImmutable())

The beauty of HIDDEN is that, to orderBy() something, that "something" needs to be selected. But without the HIDDEN, instead of returning an array of Voyage objects, Doctrine would suddenly return an array of arrays, where each array has a 0 key containing the Voyage object and an isPast key. Yikes!

Here are a few other examples shared by users:

Ordering based on State

Ordering based on the "state" of an entity thanks to @ker0x:

            WHEN voyage.state = :confirmed THEN 0
            WHEN voyage.state = :canceled THEN 1
            WHEN voyage.state = :accepted THEN 2
            WHEN voyage.state = :pending THEN 3
            ELSE 4
        END AS HIDDEN state_order'
        'confirmed' => Voyage::STATE_CONFIRMED,
        'canceled' => Voyage::STATE_CANCELED,
        'accepted' => Voyage::STATE_ACCEPTED,
        'pending' => Voyage::STATE_PENDING,
    ->orderBy('state_order', 'ASC');

Ordering Null Values Last

Ordering null values first thanks to @Thibault_1635:

    ->addSelect('CASE WHEN voyage.endAt IS NULL THEN 1 ELSE 0 END AS HIDDEN control_null_first')
    ->orderBy('control_null_first', 'DESC');

Have fun!


Sort By
Login or Register to join the conversation

Delete comment?

Share this comment

astronaut with balloons in space

"Houston: no signs of life"
Start the conversation!