I recently came to find a nice hidden feature of Doctrine2, the well named HIDDEN keyword.
I first needed to find the feature because I wanted to use functions in an order by clause.

The problem

Let’s say you want to get your user ordered by sum of comments and likes. You will write this kind of DQL:

FROM BCCModelBundle:User u
ORDER BY u.commentNumber + u.likeNumber

Sadly, for interoperability reasons, such a statement is not possible using doctrine2. You cannot have an arithmetic expression in the ORDER BY clause. This goes also with the GROUP BY

The solution: the HIDDEN keyword

Hopefully, you can use the HIDDEN keyword. This idea is to declare a new field in the SELECT clause and mark it hidden to avoid the ORM fetching it:

	u.commentNumber + u.likeNumber AS HIDDEN score
FROM BCCModelBundle:User u
ORDER BY score

Now, you have a score field which is compatible with the ORDER BY clause. I am sure you will find it very useful, especially when you will starting to have heavy DQL with complex ORDER BY clause.

EDIT: Please note that this feature is available on Doctrine 2.2+.


5 thoughts on “Doctrine2 ninja with the HIDDEN keyword

  1. Classy !

    A quick note to let you know that I finally decided to migrate my project on Symfony2. It was not an easy decision, but I feel better now 😉

    However, I may have some questions, would it be possible to call you on occasion? xx

  2. You, sir, are a genius. I stumbled across a link to this page after googling for ages around how to do more advanced ordering and HIDDEN seems to be the answer.

    There seems to be only one mention of it in the actual documentation: https://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html?highlight=HIDDEN#select-expressions

    Not very helpful!

    So, in summary: I am now using something like:

    ->addSelect(‘a.fieldName * a.nother AS HIDDEN orderField’)
    ->addOrderBy(‘orderField’, ‘ASC’)

    …and all seems good so far!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s