Search code examples
symfonydoctrinedql

How can I translate "BETWEEN" function from Mysql to Doctrine?

I'm developing some code, that needs to execute this query

(Traditional MYSQL)

SELECT * FROM `SistemaPuntuacions` WHERE `rankingActual` BETWEEN (SELECT `rankingActual` FROM `SistemaPuntuacions` WHERE `user_id` = $id) -5 AND (SELECT `rankingActual` FROM `SistemaPuntuacions` WHERE `user_id` = $id) +5 ORDER BY `rankingActual`"

Basically I've in SistemaPuntuacions a list of users, with their ranking (rankingActual). I need to show the 5 users above this users ranking and the 5 below.

But there's no "BETWEEN" in doctrine language, so I'm stuck there.

Can I get some help?

Thank you.

Solution

  • Well, actually there is a BETWEEN in DQL. This code snippet is from the official docs.

    <?php
$query = $em->createQuery('SELECT u.name FROM CmsUser u WHERE u.id BETWEEN ?1 AND ?2');
$query->setParameter(1, 123);
$query->setParameter(2, 321);
$usernames = $query->getResult();