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

Solution 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.

Solution 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

Comments

[ginolin]

but you know the answer to my general question?

[ginolin]

Ok, i initially had many errors because the word "avg" is protected. I use "score_avg" after "AS" and everything is ok, but there is another problem: how can i write in template "avg_score" and "count_score"? I have ever the same error: Notice: Array to string conversion at this line: "$result = ("Score average: ".$avgScore);"

[S.Thiongane]

try to retrieve array values by there given index in query builder: $avgScore["avg_score"] and $avgScore["count_score"]. if that doesn't work, add this piece of code before that line and post the output : var_dump($avgScore);exit;

[ginolin]

I have an error with "$avgScore["count_score"]": Undefined index: count_score at that line. If I put this dump: "var_dump($avgScore); exit;" I have: array(1) { [0]=> array(2) { ["avg_score"]=> string(6) "5.6666" ["count_score"]=> string(1) "3" } } Thanks a lor for your help.

[S.Thiongane]

sorry i just see your com. So you can get it like this : $avgScore[0]["count_score"] and $avgScore[0]["avg_score"]

[S.Thiongane]

if you don't want to have [0] before, you have to customize you select by providing entities...

[ginolin]

I have to thank you because it works. Now I get what I need, but I want to understand how it works, also for the future. Can you send me some links to study the operation of this array and how to print the query results as in this case? In short, I want to look into. Even in your last comment because I did not understand what you mean. Thanks a lot.

[S.Thiongane]

happy that helped ! Don't forget to mark the answer as correct ! I will edit it to add some explanation of how it works

[ginolin]

OK, i make my work with mark answer. Now, would you tell me how to study better "array and DQL and symfony"? Thanks friend.

[ginolin]

Can you explain better this frase: "if you don't want to have [0] before, you have to customize you select by providing entities..." thanks.

[S.Thiongane]

To better understand: multidimensional arrays, DQL, symfony (official doc)

[Justinas R.]

Unfortunately, you will get NoResultException exception if no results were found.