Symfony/Doctrine: SUM and AVG score of players

Question

I have in my database the tab: PLAYERS and a tab: SCORES.

In tab SCORES i have these rows: 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 3 scores.

The count of the scores is "17" (9+3+5).

The avg of the score of the player is "5.6" (17totscores / 3countScores).

---

I have an entity with ORM, it' ok.

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 "}]] ?????

Answer 1

what is: [[{"1":" and "}]]  ?

1 is the index of avg(g.score) in your query. To better understand why, try an echo of $queryAvgScore->getDql() before getResult().

Let's get back to the general question :

the SQL is :

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.

Hope it helps.

Answer 2

Symfony 2.6 is easy with 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