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.
Let’s say you want to get your user ordered by sum of comments and likes. You will write this kind of DQL:
SELECT u 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:
SELECT u, 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+.