MySQL Custom Variable in Doctrine and Symfony

Question

I have the following Doctrine statement that works fine.

$query = $this->createQuery('r') ->select('u.id, CONCAT(u.first_name, " ", LEFT(u.last_name,1)) as full_name, u.first_name, u.last_name, u.gender, r.run_time') ->innerJoin('r.ChallengeUser u') ->orderBy('run_time') ->execute(array(), Doctrine::HYDRATE_ARRAY_SHALLOW); 

I need to add a row counter. Now I know that with raw SQL you can do this,

 SET @rank=0; SELECT @rank: =@rank +1 as rank, u.id, u.first_name ....etc 

So my question is: how can I get this to work with Symfony 1.4 and Doctrine? I am using MySQL for this project.

---

Edit ... I figured it out.

 Doctrine_Manager::getInstance()->getCurrentConnection()->standaloneQuery('SET @rank=0;')->execute(); $query = $this->createQuery('r') ->select('r.run_time, @rank: =@rank +1 rank, r.user_id, CONCAT(u.first_name, " ", LEFT(u.last_name,1)) as full_name, u.first_name, u.last_name, u.gender') ->leftJoin('r.ChallengeUser u') ->orderBy('run_time') ->execute(array(), Doctrine::HYDRATE_ARRAY_SHALLOW); 

Add a standalone query to set the variable and replace the inner join with the left join.

Answer 1

if you use MySQL, and no switchings to other DBMSs are expected, perhaps you could try

 $query = $this->createQuery('r') ->select('COUNT(u.id) as rank, u.id, CONCAT(u.first_name, " ", LEFT(u.last_name,1)) as full_name, u.first_name, u.last_name, u.gender, r.run_time') ->innerJoin('r.ChallengeUser u') ->orderBy('run_time') ->execute(array(), Doctrine::HYDRATE_ARRAY);