Yii2 Multiple counts in a query with group by

1

Hello,

i try to get “statistics” from my database, like this :

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- exam name ----------- exam date - ---------------------------
- level - NB participant - NB succes - NB fail - NB drop out -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- B2.1  - 5              - 3         - 1       - 1           -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- B2.2  - 3              - 2         - 0       - 1           -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- B2.3  - 6              - 2         - 2       - 2           -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

and i would have a “table” like this for each exam,

so i did my query with some count and group by :

$data = Assures::find() 
        ->select([
           'ass_prenom','ass_nom','ass_lang_cours','ass_state',
           'ass_global_level','ass_startlevel_ecrit','ass_dateh_cours_start','ass_dateh_cours_fin',
           'exam_nom','exam_dateh_ecrit','exam_state',
            'SUM( IF(exam_state = "succes", 1, 0) ) AS cntsucces', 
            'SUM( IF(exam_state = "echec", 1, 0) ) AS cntechec', //fail
            'SUM( IF(ass_state = "interruption", 1, 0) ) AS cntabandon',  //drop out
            'COUNT(*) AS cnt'
        ])
        //->from('assures')
        ->where('assures.ass_global_level != \'\'')
        ->orderBy(['ass_lang_cours'=>SORT_DESC,'exam_dateh_ecrit'=>SORT_ASC,'exam_nom'=>SORT_ASC,'ass_global_level'=>SORT_ASC])
        ->groupBy('ass_global_level','exam_dateh_ecrit','exam_nom')
        ->all();

And then i tried to create the “table” in my view :

 <?php
            
            $exam = '';
            $date = '';
            $enresult = '';
            $deresult = ''; 
            foreach ($model as $item) {
                if($item->ass_lang_cours === 'en'){
                    if($exam != $item->exam_nom OR $date != $item->exam_dateh_ecrit)
                    {
                        $enresult .= '<br><b>Exam : '. $item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>';   
                    }
                    $enresult .=  '<li>';
                    $enresult .=  'Niveau: '.$item->ass_global_level.' | ';
                    $enresult .=  'Partipants: '.$item->cnt.' | ';
                    $enresult .=  'Réussite: '.$item->cntsucces.' | ';
                    $enresult .=  'Echec: '.$item->cntechec.' | ';
                    $enresult .=  'Abandons: '.$item->cntabandon.' | ';
                    $enresult .=  '</li>';
                }
                if($item->ass_lang_cours === 'de'){
                    if($exam != $item->exam_nom OR $date != $item->exam_dateh_ecrit)
                    {
                        $deresult .= '<br><b>Exam : '. $item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>';   
                    }
                    $deresult .=  '<li>';
                    $deresult .=  'Niveau: '.$item->ass_global_level.' | ';
                    $deresult .=  'Partipants: '.$item->cnt.' | ';
                    $deresult .=  'Réussite: '.$item->cntsucces.' | ';
                    $deresult .=  'Echec: '.$item->cntechec.' | ';
                    $deresult .=  'Abandons: '.$item->cntabandon.' | ';
                    $deresult .=  '</li>';
                }

                $exam = $item->exam_nom;
                $date = $item->exam_dateh_ecrit;

            }
          
            ?>

            <h4>Anglais</h4>
            <ul>
            <?= $enresult;?>
            </ul>
            <h4>Allemand</h4>
            <ul>
            <?= $deresult;?>
            </ul>

And this is how it looks like :

19
191162×1118 42.8 KB

But, i don’t get the all the levels for each exam, and i don’t get all exams, if 2 exams are in the same date for exemple, i get only one of them and all participants go in it…

and i tryed to use a simple query

$data = Assures::find()
        ->where('assures.ass_global_level != \'\'')
->orderBy(['ass_lang_cours'=>SORT_DESC,'exam_nom'=>SORT_ASC,'exam_dateh_ecrit'=>SORT_ASC,'ass_global_level'=>SORT_ASC])->all();

and then do a foreach and some if, but i allways get wrong datas and problems with first and last entries…

If anyone has the answer ^^ Thank you very much for your help !

2

So, i don’t know if this is a good solution, but i succeed with this code :

in my controller :

public function actionRapports(){

    $model = Assures::find()  
        ->select([
            'GROUP_CONCAT("+ ", ass_prenom," ", ass_nom ," || ( ", ass_state ," ) ", " Niv.  ", ass_global_level, " - ", exam_nom, " Ecrit: ", exam_dateh_ecrit, " Oral: ",exam_dateh_oral, " || ", exam_state SEPARATOR " <br> ") AS full_name',
            'exam_nom',
            'exam_dateh_ecrit',
            'ass_lang_cours',
            'ass_state',
            'ass_global_level',
            'SUM( IF(exam_state = "succes", 1, 0) ) AS cntsucces', 
            'SUM( IF(exam_state = "echec", 1, 0) ) AS cntechec', 
            'SUM( IF(ass_state = "interruption", 1, 0) ) AS cntabandon',
            'COUNT(*) AS cnt']
            ) 
        ->where(['or',['ass_state'=>'tobook'],['ass_state'=>'formation'],['ass_state'=>'examen'],['ass_state'=>'interruption']])
        ->groupBy(['exam_nom','ass_lang_cours','exam_dateh_ecrit','ass_global_level'])
        ->orderBy(['ass_lang_cours'=>SORT_ASC,'exam_dateh_ecrit'=>SORT_ASC])
        ->all();

    return $this->render('rapports', [
        'model' => $model,
    ]);

}

Then in my view

<?php

            $exam = '';
            $date = '';
            $enresult = '';
            $deresult = ''; 
            foreach ($model as $item) {
              if(!empty($item->ass_global_level)){ 
                if($item->ass_lang_cours === 'en'){
                    if($exam != $item->exam_nom OR $date != $item->exam_dateh_ecrit)
                    {
                        $enresult .= '<br><b>Exam : '. $item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>';   
                    }
                    $enresult .=  '<li>';
                    $enresult .=  'Niveau: '.$item->ass_global_level.' | ';
                    $enresult .=  'Partipants: '.$item->cnt.' | ';
                    $enresult .=  'Réussite: '.$item->cntsucces.' | ';
                    $enresult .=  'Echec: '.$item->cntechec.' | ';
                    $enresult .=  'Abandons: '.$item->cntabandon.' | ';
                    $enresult .=  '<a value="'.$item->full_name.'" class="showModalButton" title="'. $item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'">Liste participants</a></li>';
                }
                if($item->ass_lang_cours === 'de'){
                    if($exam != $item->exam_nom OR $date != $item->exam_dateh_ecrit)
                    {
                        $deresult .= '<br><b>Exam : '. $item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>';   
                    }
                    $deresult .=  '<li>';
                    $deresult .=  'Niveau: '.$item->ass_global_level.' | ';
                    $deresult .=  'Partipants: '.$item->cnt.' | ';
                    $deresult .=  'Réussite: '.$item->cntsucces.' | ';
                    $deresult .=  'Echec: '.$item->cntechec.' | ';
                    $deresult .=  'Abandons: '.$item->cntabandon.' | ';
                    $deresult .=  '<a value="'.$item->full_name.'" class="showModalButton" title="'. $item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'">Liste participants</a></li>';
                }

                $exam = $item->exam_nom;
                $date = $item->exam_dateh_ecrit;
              }
            }
            
            ?>

            <h4>Anglais</h4>
            <ul>
            <?= $enresult;?>
            </ul>
            <h4>Allemand</h4>
            <ul>
            <?= $deresult;?>
            </ul>

The result :

22
22589×674 49.7 KB

3

I suggest use SqlDataProvider with ListView