Doctrine2 ninja with the HIDDEN keyword

Doctrine2 ninja with the HIDDEN keyword

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:

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+.

Advertisements