Aggregate function dosen't work with QB statement .

Question

I just want to know how much the student with the (id = 1) have tests passed. In my DB the student with the id = 1 already passed One test.

DB and the runing result in link below :

Capture

The GetAvg() methode in TestRepository do the count of test have passed by student 1 .

NoteMatiereController :

     public  function showMarksByStudentAction($idMat)

{    $iduser=$this->getUser()->getId();

         $em = $this->getDoctrine()->getManager(); 
    $notes = $em->getRepository("AcmeMyBundle:NoteMatiere")
            ->showMarksByStudent($iduser, $idMat);

      $moys = $em->getRepository("AcmeMyBundle:Test")
            ->GetAVG();

     print_r ($moys);
 return($this->render("AcmeMyBundle:NoteMatiere:listNotes.html.twig", array("notes" => $notes,"moys"=>$moys)));


} 

NoteMatiereRepository :

     public function showMarksByStudent($student_id,$mat_id)
{ 
    $query=$this->getEntityManager()
        ->createQuery("SELECT n from AcmeMyBundle:Etudiant e ,AcmeMyBundle:NoteMatiere n,AcmeMyBundle:Test t,AcmeMyBundle:MatiereProf m WHERE n.idEt=?1 and t.idTest = n.idTest and t.idMatProf = m.id and m.idMat=?2")
        ->setParameter(1, $student_id)
        ->setParameter(2, $mat_id);
    return $query->getResult();   

}

TestRepository :

  public function GetAVG()
{         
  $qb = $this->createQueryBuilder('m');
    $qb->select('n.idEt,t.idTest,t.coefTest,count(t.coefTest) as ts')
            ->from('AcmeMyBundle:Test', 't')
            ->from('AcmeMyBundle:NoteMatiere', 'n')
            ->where('n.idEt=1 and t.idTest = n.idTest');
        // -> and where(t.idTest = n.idTest) ; is the same   
    return $qb->getQuery()->getResult();


}

without adding to condition t.idTest = n.idTest the result for the count will be 16 !

ListNote.html.twig : It will help you to understand the view how is working .

<table border=1>
  <tr>
    <!--   <th>Description</th>
        <th>Note</th>
         !-->
    <th>Liste des Tests</th>
    <th>Note</th>
  </tr>
  {% for note in notes %}
  <tr>
    <td>{{ note.idTest }}</td>
    <td>{{ note.note }}</td>


  </tr>
  {%endfor%}

</table>
<table>
  {% for moy in moys %}

  <td>id Test ==>{{ moy.idTest }}</td>
  <td>Coef Test ==>{{ moy.coefTest }}</td>
  <td>Count Coef Test ==>{{ moy.ts }}</td>


  {%endfor%}


</table>

My statement is so long then that but after a long tests the problem was from there , I realy get stacked there . THANKS FOR HELP !

Answer 1

Try this:

public function GetAVG()
{         
  $qb = $this->createQueryBuilder('test');

  $qb->select('nota.idEt,test.idTest,test.coefTest,count(test.coefTest) as ts')
     ->join('test.noteMaterie', 'nota', Join::WITH. 'test.idTest=nota.idTest') //You can skip the 4 param here if its the regular realtionship parameter
     ->where('nota.idEt = 1');  

   return $qb->getQuery()->getResult();
}

Change relations as they are named in your case please!