本文介绍了从子查询中选择原则的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试通过学说运行以下查询:
I am trying to run the following query via doctrine:
$sql = "SELECT league_id, sum(num_fans) AS total_fans FROM ("
."SELECT COUNT(*) as num_fans, t.id AS team_id, l.id AS league_id FROM fan_link fl "
."JOIN team t ON fl.team_id = t.id JOIN League l ON t.league_id = l.id GROUP BY fl.team_id"
.") a GROUP BY league_id LIMIT 0, 3";
$results = $this->em->createQuery($sql)->getScalarResult();
该查询在PHPMyAdmin中工作正常,但是当我尝试在主义中运行它时,出现此错误:
The query works fine in PHPMyAdmin, but when I try to run it in doctrine I get this error:
Doctrine\ORM\Query\QueryException [ 0 ]:
[Semantical Error] line 0, col 51 near '(SELECT COUNT(*)':
Error: Class '(' is not defined.
括号在理论查询中是(
的保留字符吗?我在 where
子句中有子选择项,但在其他地方都可以正常工作,但在子句中有一个子选择项
子句不想工作,我在做什么错了?
Is the parenthesis (
a reserved character in doctrine queries? I have subselects in where
clauses elsewhere that work fine, but this one in the from
clause doesn't want to work. What am I doing wrong?
更新:
我只是尝试使用查询生成器,并得到了相同的错误.代码:
Update:
I just tried using the query builder and got the same error. Code:
$qb = $this->em->createQueryBuilder();
$leagueQuery = $qb->select('league_id, sum(num_fans) AS total_fans')
->from("(SELECT count(*) as num_fans, t.id AS team_id, l.id AS league_id "
."FROM fan_link fl "
."JOIN team t ON fl.team_id = t.id "
."JOIN League l ON t.league_id = l.id GROUP BY fl.team_id)",
'a')
->groupBy("league_id")
->orderBy("total_fans", "DESC")
->setMaxResults(3)
->getQuery();
$results = $leagueQuery->getArrayResult();
推荐答案
最终使用此:
$sql = "SELECT league_id, sum(num_fans) AS total_fans FROM "
."(SELECT COUNT(*) as num_fans, t.id AS team_id, l.id AS league_id FROM fan_link fl "
."JOIN team t ON fl.team_id = t.id JOIN League l ON t.league_id = l.id GROUP BY fl.team_id"
.") a GROUP BY league_id LIMIT 0, 3";
$q = $this->em->getConnection();
$league_results = $q->fetchAll($sql);
这篇关于从子查询中选择原则的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!