Yii2 foreach get data inside groupby query


(Perrouddavid) #1

Hello,

i have a little question here,

i did a query with groupby and count(*) to get datas… everything works fine, but
now i need to list the names of the clients, and i don’t find how to do it.

Here is the query :

$model = Assures::find()
        ->select(['ass_prenom','ass_nom','exam_nom','exam_dateh_ecrit','ass_lang_cours','ass_state','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'])
        ->orderBy(['ass_lang_cours'=>SORT_ASC,'ass_date_test_mesure'=>SORT_ASC])
        ->all();

and this is the foreach in my view

<?php
$listone = '';
$listtwo = '';

//'exam_nom','exam_dateh_ecrit','ass_lang_cours','ass_nom',$cnt;
	foreach ($model as $item) {
		if(!empty($item->exam_dateh_ecrit)){
			switch ($item->exam_dateh_ecrit) {
			    case '2019-09-21': $limit = '24 août 2019'; break;
			    case '2019-09-27': $limit = '24 août 2019'; break;
			    case '2019-10-19': $limit = '21 septembre 2019'; break;
			    case '2019-10-26': $limit = '21 septembre 2019'; break;
			    case '2019-11-22': $limit = '25 octobre 2019'; break;
			    case '2019-11-23': $limit = '25 octobre 2019'; break;
			    case '2019-12-05': $limit = '? '; break;
			    case '2019-12-14': $limit = '? '; break;
			    case '2019-10-04': $limit = '6 septembre 2019'; break;
			    case '2019-11-01': $limit = '5 octobre 2019'; break;
			    case '2019-12-06': $limit = '8 novembre 2019'; break;
			    }
			if($item->ass_lang_cours == 'en'){
				
				$listone .= '<li><b>'.$item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>
				<br>Limite d\'inscription: '.$limit.'<br><span>Nombre de participants: '.$item->cnt.'</span>
				<br>'.$item->ass_prenom.' '.$item->ass_nom.'</li>'; 
			}
			if($item->ass_lang_cours == 'de'){
				
				$listtwo .= '<li><b>'.$item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>
				<br>Limite d\'inscription: '.$limit.'<br><span>Nombre de participants: '.$item->cnt.'</span>
				<br>'.$item->ass_prenom.' '.$item->ass_nom.'</li>';
			}
		}
	}
?>

What i’m looking to do is to get the ass_prenom and ass_nom of each items

Any ideas ? Thank you very much !


(Razon Yang) #2

If I didn’t misunderstand, you could change the select parameter if you are using MySQL:

->select(['ass_prenom','ass_nom','exam_nom','exam_dateh_ecrit','ass_lang_cours','ass_state','COUNT(*) AS cnt'])

to

->select([
    'ass_prenom' =>  new \yii\db\Expression('GROUP_CONCAT(ass_prenom SEPARATOR ",")'),
    'ass_nom' =>  new \yii\db\Expression('GROUP_CONCAT(ass_nom SEPARATOR ",")'),
    'exam_nom','exam_dateh_ecrit','ass_lang_cours','ass_state','COUNT(*) AS cnt'
])

And then, you could use it in view, otherwise, you can also
convert those info to array:

$prenoms = explode(',', $item->ass_prenom);
foreach ($prenoms as $pregnom) {
     //...
}
$noms = explode(',', $item->ass_nom);
foreach ($noms as $nom) {
     //...
}

Change separator(,) as needed.


(Perrouddavid) #3

Hello, thank you very much for your help !

I have to say, i don’t understand your answer, i changed my select as you said, but when in my view i try

$prenoms = implode(',', $item->ass_prenom);

$noms = implode(',', $item->ass_nom);`

I just get an error : implode(): Invalid arguments passed

What i want is list “Prenom Nom” of participants , where you see the red line on my print screen

Is this helps to understand ?


(Razon Yang) #4

Sorry, my fault, it is explode, not implode.

I’ve updated my answer.


(Perrouddavid) #5

Hey, thanks! I thought about this and i tried with implode, but i’didn’t had time to come back to write it ^^

But, i coudn’t find the way to get prenom1 nom1, prenom2 nom2, prenom3 nom3 etc. with this method, do you know how i could get rely the prenom and nom (firstname lastname) and not prenom1 prenom2 prenom3 then nom1 nom2 nom3 ?


(Perrouddavid) #6

OK, i found a solution :slight_smile: Thank you @razonyang to put me on the good line !

So, my query :

$model = Assures::find()  
        ->select([
            'GROUP_CONCAT("+ ", ass_prenom," ", ass_nom ," ( ", ass_state ," ) " SEPARATOR " <br> ") AS full_name',
            'exam_nom',
            'exam_dateh_ecrit',
            'ass_lang_cours',
            'ass_state',
            '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'])
        ->orderBy(['ass_lang_cours'=>SORT_ASC,'exam_dateh_ecrit'=>SORT_ASC])
        ->all();

And my view

foreach ($model as $item) {
		if(!empty($item->exam_dateh_ecrit)){
			switch ($item->exam_dateh_ecrit) {
			    case '2019-09-21': $limit = '24 août 2019'; break;
			    case '2019-09-27': $limit = '24 août 2019'; break;
			    case '2019-10-19': $limit = '21 septembre 2019'; break;
			    }


			if($item->ass_lang_cours == 'en'){
				//$fullname = explode(',', $item->full_name);
				$listen .= '<li><b>'.$item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>
				<br>Limite d\'inscription: '.$limit.'<br><span>Nombre de participants: '.$item->cnt.'</span>
				<br>'.$item->full_name.'</li>'; 

                $i = $i + $item->cnt;
                
			}
			if($item->ass_lang_cours == 'de'){
				//$fullname = explode(',', $item->full_name);
				$listde .= '<li><b>'.$item->exam_nom.' '.date('d.m.Y',strtotime($item->exam_dateh_ecrit)).'</b>
				<br>Limite d\'inscription: '.$limit.'<br><span>Nombre de participants: '.$item->cnt.'</span>
				<br>'.$item->full_name.'</li>';
               $i = $i + $item->cnt;
			}
		}
        
	}

And the result :