MySQL用户定义的变量在Doctrine和Symfony [英] MySQL User defined variable within Doctrine and Symfony
本文介绍了MySQL用户定义的变量在Doctrine和Symfony的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
$ query = $ this-> createQuery('r' )
- > select('u.id,CONCAT(u.first_name,,LEFT(u.last_name,1))为full_name,u.first_name,u.last_name,u.gender,r。
- > innerJoin('r.ChallengeUser u')
- > orderBy('run_time')
- > execute(array(),Doctrine :: HYDRATE_ARRAY_SHALLOW) ;
我需要在此添加一个行数。现在我用原始SQL知道你可以做到这一点;
SET @ rank = 0;
SELECT @rank:= @ rank + 1 as rank,u.id,u.first_name .... etc
所以我的问题是,我怎么能用Symfony 1.4和Doctrine来运行呢?
我在这个项目中使用MySQL。
编辑...
我想出了
Doctrine_Manager :: getInstance() - > getCurrentConnection() - > standaloneQuery('SET @ rank = ) - >执行();
$ query = $ this-> createQuery('r')
- > select('r.run_time,@rank:= @ rank + 1 rank,r.user_id, CONCAT(u.first_name,,LEFT(u.last_name,1))作为full_name,u.first_name,u.last_name,u.gender')
- > leftJoin('r.ChallengeUser u')
- > orderBy('run_time')
- > execute(array(),Doctrine :: HYDRATE_ARRAY_SHALLOW);
添加独立查询以设置变量,并将内部连接交换为左连接。 / p>
解决方案
如果您使用MySQL,并且没有预见到其他DBMS的切换,也许您可以尝试
$ query = $ this-> createQuery('r')
- > select('COUNT(u.id)as rank, u.id,CONCAT(u.first_name,,LEFT(u.last_name,1))为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);
I have the following Doctrine statement which 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 count into this. Now I know 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 run 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 the standalone query in to set the variable, and swap the inner join to a left join.
解决方案
if you are using MySQL, and no switches to other DBMS are foreseen, maybe 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);
这篇关于MySQL用户定义的变量在Doctrine和Symfony的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文