mysql - 聚合函数不适用于 QB 语句。

Question

我只想知道 (id = 1) 的学生通过了多少考试。在我的数据库中，id = 1 的学生已经通过了一次测试。

数据库和运行结果在下面的链接中:

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();   

}

测试库:

  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();


}

如果不添加条件 t.idTest = n.idTest，计数结果将为 16!

ListNote.html.twig:它将帮助您了解 View 的工作原理。

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

我的陈述太长了，但经过长时间的测试，问题就出在这里，我真的被堆在那里了。感谢您的帮助!

Answer 1

试试这个:

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();
}

请更改关系，因为它们在您的案例中被命名!