phpsymfonyhighchartscountrepository

creating a chart using a count query on repository in symfony


I'm working on symfony and I'm trying to count the number of employees in every team I have in my database and show them into a chart. So I created this query in the repository:

function findNbEmp()
{
$query = $this->getEntityManager()
    ->createQuery("Select COUNT(distinct u.id) AS nbEmp, e.nomEq as equipe
                               FROM MyAppEspritBundle:User u INNER JOIN 
MyAppEspritBundle:Equipe e
                               WITH u.id_equipe=e.idEq
                               GROUP BY u.id_equipe");

return $query->getResult();


}

and then went to the controller and have done this:

public function chartLineAction()
{

   $em = $this->getDoctrine()->getManager();

    $nbemps = $em->getRepository('MyAppEspritBundle:User')->findNbEmp();

    var_dump($nbemps);

    $tab = array();
    $categories = array();

    foreach ($nbemps as $nb) {
      //  var_dump($user['nbEmp']);
        array_push($tab,$nb['nbEmp'] );
      array_push($categories, $nb['equipe']);
    }


    // Chart
   $series = array(
        array("name" => "Nb employés", "data" => array($tab))
   );

    $ob = new Highchart();
    $ob->chart->renderTo('linechart');        //  #id du div où afficher le graphe
    $ob->title->text('Nombre d employés par équipe');
    $ob->xAxis->title(array('text' => "Equipe"));
    $ob->yAxis->title(array('text' => "Nb Employés"));
    $ob->xAxis->categories($categories);
    $ob->series($series);

    return $this->render('MyAppEspritBundle:Gerant:AccueilGerant.html.twig',
        array(
        'chart' => $ob
    ));
}

The problem is when I put $nb['nbEmp'] in array_push($tab, $nb['nbEmp']), the graph just goes away so when I let only ($tab, $nb), at least I can see the names of groups that I've made and tooken from the database. BTW, everything is working fine in the bundle excepting the first array_push (because of nbEmp)

Even I've used var_dump, it showed me the result that I want to set on my graph but couldn't:

enter image description here

I'm wondering, what is missing in my code so it could read nbEmp properly in the graph. Can you guys help me? I'm open to other solutions too if they are more effective than this one I'm trying to achieve. Please, I need to know what should I put in that first array_push?

Thanks in advance.


Solution

  • public function chartLineAction()
    {
    
       $em = $this->getDoctrine()->getManager();
        // $users is not the best name for this result 
        $users = $em->getRepository('MyAppEspritBundle:User')->findNbEmp();
    
      //  var_dump($users);
    
        $tab = array();
        $categories = array();
    
        foreach ($users as $user) {
          $tab[] = (int)$user['nbEmp']
        }
    
    
        // Chart
       $series = array(
            array("name" => "Nb employés", "data" => array($tab))
       );
    
        $ob = new Highchart();
        $ob->chart->renderTo('linechart');       
        $ob->title->text('Nombre d employés par équipe');
        $ob->xAxis->title(array('text' => "Equipe"));
        $ob->yAxis->title(array('text' => "Nb Employés"));
       // $ob->xAxis->categories($categories);
        $ob->series($tab);
    
        return $this->render('MyAppEspritBundle:Gerant:AccueilGerant.html.twig',
            array(
            'chart' => $ob
        ));
    }
    

    Try the code above , it should show you a result but you probably want to display 'equipe' ids ( or names ) as well with the number of employees. For that you need to modify your query like this :

    function findNbEmp()
    {
    $query = $this->getEntityManager()
        ->createQuery("Select COUNT(distinct u.id) AS nbEmp, u.id_equipe as id_equipe
                                   FROM MyAppEspritBundle:User u
                                   GROUP BY u.id_equipe");
    
    return $query->getResult();
    }
    

    and then fill your $categories with $users['id_equipe'] and uncomment the line that I commented :

    // $ob->xAxis->categories($categories);
    

    Try the first part of my suggestion then if you get a chart , you will be more confortable modifying and playing with different options.