SQL Symfony/Doctrine：玩家的 SUM 和 AVG 得分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20023426/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Symfony/Doctrine: SUM and AVG score of players
提问by ginolin
I have in my database the tab: PLAYERS and a tab: SCORES.
我的数据库中有标签：PLAYERS 和标签：SCORES。
In tab SCORES i have these rows: ID - IDPLAYER - SCORE
在选项卡 SCORE 中，我有这些行：ID - IDPLAYER - SCORE
For example:
例如：
ID IDPLAYER SCORE
---------------------
1 1 5
2 2 4
3 1 3
4 2 1
5 1 9
I want put in a template this:
我想放入一个模板：
For "player 1" there are 3scores.
对于“玩家 1”，有3 个分数。
The countof the scores is "17" (9+3+5).
分数的计数为“ 17”（9+3+5）。
The avgof the score of the player is "5.6" (17totscores / 3countScores).
玩家得分的平均值为“ 5.6”（17totscores / 3countScores）。
I have an entity with ORM, it' ok.
我有一个带有 ORM 的实体，没关系。
I have a controller with this function:
我有一个具有此功能的控制器：
public function avgScoreAction($id) {
$queryScore = $this->getDoctrine()
->getRepository('AcmeBundle:tabScores');
$queryAvgScore = $queryScore->createQueryBuilder('g')
->select("avg(g.score)")
->where('g.idPlayer = :idPlayer')
->setParameter('idPlayer', $id)
->getQuery();
$avgScore = $queryAvgScore->getResult();
$result = ("Score average: ".$avgScore);
return new Response($result);
But I have an error:
但我有一个错误：
"Notice: Array to string conversion in this line:"
$result = ("Score average: ".$avgScore);
If I write this:
如果我这样写：
$response = new Response();
$response->setContent(json_encode(array($avgScore)));
$response->headers->set('Content-Type', 'application/json');
return $response;
I get this:
我明白了：
[[{"1":"5.6667"}]]
which is the correct avg, but what is: [[{"1":" and "}]] ?????
这是正确的平均值，但什么是： [[{"1":" 和 "}]] ?????
回答by S.Thiongane
what is: [[{"1":" and "}]] ?
1is the index of avg(g.score)in your query. To better understand why, try an echoof $queryAvgScore->getDql()before getResult().
1是avg(g.score)您查询中的索引。为了更好地理解原因，请尝试使用echoof $queryAvgScore->getDql()before getResult()。
Let's get back to the general question :
让我们回到一般问题：
the SQL is :
SQL是：
SELECT AVG(SCORE) as AVG, COUNT(SCORE) as COUNT, IDPLAYER as PLAYER FROM SCORES GROUP BY IDPLAYER
and now with query builder :
现在使用查询构建器：
$queryAvgScore = $queryScore->createQueryBuilder('g')
->select("avg(g.score) as score_avg, count(g.score) as score_count")
->where('g.idPlayer = :idPlayer')
->groupBy('g.idPlayer')
->setParameter('idPlayer', $id)
->getQuery();
Notice that i have added aliases, this is better than using indexes.
请注意，我添加了aliases，这比使用索引更好。
Hope it helps.
希望能帮助到你。
回答by maccevedor
Symfony 2.6 is easy with DQL
Symfony 2.6 使用 DQL 很容易
$dql = "SELECT SUM(e.amount) AS balance FROM Bank\Entities\Entry e " .
"WHERE e.account = ?1";
$balance = $em->createQuery($dql)
->setParameter(1, $myAccountId)
->getSingleScalarResult();
Info:
信息：
http://doctrine-orm.readthedocs.org/en/latest/cookbook/aggregate-fields.html?highlight=sum
http://doctrine-orm.readthedocs.org/en/latest/cookbook/aggregate-fields.html?highlight=sum
