Sorting by Count of Related Items in CGridView

Yii 1.1 General Discussion
1

Hi all,

I am following this wiki by softark for "Searching and Sorting by Count of Related Items in CGridView"

Everything works fine, but I have problems when setting it as a sorting filter outside CGridView.

in _search:




<div class="row">

		<?php echo $form->label($model,'Sorting');?>

		<?php echo $form->dropDownList($model, 'order', $model->columnOrder(),array('empty'=>''));?>

	</div>

in model:




public function columnOrder(){

		return array(

			'1'=>'Schulung',

			'2'=>'Termin',

			'3'=>'Anmeldungen'

		);

	}




//and in search function:




$sort = new CSort();

		

		switch ($this->order){

			case '1':

				$sort->defaultOrder=array('sch_Name'=>CSort::SORT_ASC);

				break;

			case '2':

				$sort->defaultOrder=array('dAnfang'=>CSort::SORT_ASC);

				break;

			case '3':

				$sort->defaultOrder=array('registered'=>CSort::SORT_ASC);

			default:

				$sort->defaultOrder=array('cSchulungsTerminID'=>CSort::SORT_DESC);

		}


                ...

                ...

                ...

For all the cases it works fine (when user selects from dropdownlist, sorting is done accordingly) except "case 3".

Where ‘registered’ is a count column (like in the wiki)




$registered_sql="(select count(cTeilnehmerID) from buchung b where b.cSchulungsTerminID=t.cSchulungsTerminID)";

		$criteria->select=array(

			'*',

			$registered_sql.' as registered'

		);

		$criteria->compare($registered_sql,$this->registered);

Only in this case the sorting is not done…

Can anyone help?

Thank you in advance.

2

Hi erand,

Thank you for reading the wiki.

Would you please check the following?

  1. Does case 1 or case 2 work fine AFTER you have sorted the grid by clicking on a header cell?

  2. Doesn’t case 3 work BEFORE you sort the grid by clicking on a header cell?

  3. Try disabling the sorting by the header cells by specifying &#036;sort-&gt;attributes = array('');, and see what will happen.

I think the defaultOrder will not take effect when a sorting parameter (e.g. "sort=sch_Name.desc") is already in the query string. After you have sorted the grid by clicking on a cell in the header row, such a sorting parameter will override the default order.

The following may work, even with the default sorting functionality of the grid being enabled:




switch ($this->order){

    case '1':

        $_GET['sort'] = 'sch_Name';

        break;

    case '2':

        $_GET['sort'] = 'dAnfang';

        break;

    case '3':

        $_GET['sort'] = 'registered';

        break;

    default:

        $_GET['sort'] = 'cSchulungsTerminID.desc';

        break;

}

Or, in the view, you may create a plain dropdown (without the corresponding model) that will send a sorting parameter:




<div class="row">

    <?php echo CHtml::label('Sorting');?>

    <?php echo CHtml::dropDownList('sort', '', array(

        'cSchulungsTerminID.desc' => '',

        'sch_Name' => 'Schulung',

        'dAnfang' => 'Termin',

        'registered' => 'Anmeldungen',

    ));?>

</div>

This might be more straightforward, because you don’t need ‘order’ attribute for the model any more.

3

Hi softark,

Thank you for your reply.

  1. No they don’t work after sorting by clicking header cell…

  2. No it doesn’t work even before sorting via the header cell…(while the other worked fine before)

  3. If I disable sorting by the header cells then even the sorting with the cases for default order do not work…

Yes, like this all the sorting cases are working, but the sorting via the header cell is not working…I can

click it but it is not sorting…

And this was exactly what I needed :) everything is working fine

Thank you very much for your help :)

4

Hi softark,

I have another question:

How do I show the value for this count column in view.php?

Thank you in advance.