MySQL用户在Doctrine和Symfony中定义的变量

I have the following Doctrine statement which works fine.

我有以下Doctrine声明,它可以正常工作。

$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 count into this. Now I know with raw SQL you can do this;

我需要在此添加行计数。现在我知道使用原始SQL你可以做到这一点;

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 run with Symfony 1.4 and Doctrine? I am using MySQL for this project.

所以我的问题是,如何让它与Symfony 1.4和Doctrine一起运行?我在这个项目中使用MySQL。

---

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 the standalone query in to set the variable, and swap the inner join to a left join.

添加独立查询以设置变量,并将内部联接交换为左联接。

1 个解决方案

#1

if you are using MySQL, and no switches to other DBMS are foreseen, maybe you could try

如果您正在使用MySQL,并且没有预见到其他DBMS的切换,也许您可​​以尝试

$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);