InnerJoin 中的教义子查询
我在 MySql 中有功能查询:
select t.won,e.etot, s.username,s.country,r.points,r.rank FROM sf_guard_user sINNER JOIN 标记 r ON r.user_id = s.id内连接 (select t.user_id,count(t.user_id)as won from topper t group by t.user_id) t on t.user_id=s.id内连接(从 e.user_id 的考试 e 组中选择 e.user_id,count(e.user_id)as etot)e on e.user_id=s.idORDER BY r.rank asc
我在 sfGuardUserTable 类中编写了学说代码:
$q= $this->createQuery('u');$wq =$q->createSubquery()->select('t.user_id,count(t.user_id) 不会')->from('Topper t')->groupBy('t.user_id');$dq = $q->createSubquery()->select('e.user_id,count(e.user_id) etot')->from('考试 d')->groupBy('d.user_id');$q->select('t1.won, e1.dtot, u.username,u.country,r.points,r.rank')->innerJoin ('u.Marks r ON r.user_id = u.id')->innerJoin ('u.('.$wq->getDql().') t1 on t1.user_id=u.id')->innerJoin ('u.'.$dq->getDql().' e1 on e1.user_id=u.id')->orderBy('r.rank asc');//echo $q->getSql();返回 $q;
它给出错误找不到类 SELECT :(请帮助我.在此先感谢.
解决方案我尝试通过 Doctrine Row Sql 查询选项及其工作直接执行我的自定义查询 :)我喜欢在这里分享:
$q = new Doctrine_RawSql();$q->select('{t.won}, {e.etot}, {s.username},{s.country},{r.marks},{r.ranks}');$q->from('sf_Guard_User s INNER JOIN 标记 r ON r.user_id = s.id 内连接 (select t.id,t.user_id,count(t.user_id) as won from topper t group by t.user_id) t on t.user_id=s.id inner join (select d.id,d.user_id,count(e.user_id)as etot 从exams e group by e.user_id) e on e.user_id=s.id ORDER BYr.rank asc');$q->addComponent('s', 'sfGuardUser s');$q->addComponent('r', 's.Marks r');$q->addComponent('t', 's.Topper t');$q->addComponent('e', 's.Exams e');返回 $q;
更多帮助阅读.p>
i have functional query in MySql:
select t.won,e.etot, s.username,s.country,r.points,r.rank FROM sf_guard_user s
INNER JOIN marks r ON r.user_id = s.id
inner join (select t.user_id,count(t.user_id)as won from topper t group by t.user_id) t on t.user_id=s.id
inner join (select e.user_id,count(e.user_id)as etot from exams e group by e.user_id) e on e.user_id=s.id
ORDER BY r.rank asc
i wrote doctrine code in sfGuardUserTable Class:
$q= $this->createQuery('u');
$wq =$q->createSubquery()
->select('t.user_id,count(t.user_id) won')
->from('Topper t')
->groupBy('t.user_id');
$dq = $q->createSubquery()
->select('e.user_id,count(e.user_id) etot')
->from('Exams d')
->groupBy('d.user_id');
$q->select(' t1.won, e1.dtot, u.username,u.country,r.points,r.rank')
->innerJoin ('u.Marks r ON r.user_id = u.id')
->innerJoin ('u.('.$wq->getDql().') t1 on t1.user_id=u.id')
->innerJoin ('u.'.$dq->getDql().' e1 on e1.user_id=u.id')
->orderBy('r.rank asc');
//echo $q->getSql();
return $q;
it is giving error Couldn't find class SELECT :( please help me. Thanks in Advance.
解决方案i tried to execute my custom query directly by Doctrine Row Sql query option and its work :) i loved to share it here :
$q = new Doctrine_RawSql();
$q->select('{t.won}, {e.etot}, {s.username},{s.country},{r.marks},{r.ranks}');
$q->from('sf_Guard_User s INNER JOIN marks r ON r.user_id = s.id inner join (select t.id,t.user_id,count(t.user_id)as won from topper t group by t.user_id) t on t.user_id=s.id inner join (select d.id,d.user_id,count(e.user_id)as etot from exams e group by e.user_id) e on e.user_id=s.id ORDER BY r.rank asc');
$q->addComponent('s', 'sfGuardUser s');
$q->addComponent('r', 's.Marks r');
$q->addComponent('t', 's.Topper t');
$q->addComponent('e', 's.Exams e');
return $q;
for more help read it.
相关文章