Append a variable to CHtml::listData value

Yii 1.1 General Discussion
1

I use a listData function to provide the data for a dropDownList. I recently updated my query to provide a count, as follows:


return CHtml::listData(Town::model()->findAll(array(

			'select'=>'town.id, town.name, count(supp.id) AS NumSupplier',

			'join'=>'LEFT OUTER JOIN supplier supp ON supp.town = town.id',

			'group'=>'town.id',

			'alias'=>'town',

			'order'=>'name',

			'condition'=>'region=:region',

			'params'=>array(':region'=>$_POST['supplier_region'])

			)),

		'id', 'name');

Basically I have the SQL variable NumSupplier which stores the count value, I want this value to be appended to ‘name’. For example the current output of the listData is:

<option value="1">Manchester</option>

This now needs to be displayed as:

<option value="1">Manchester (20)</option>

2

I suposse that you actually have a public property in your Town model called NumSupplier.

So, next step is to define a new function,




public function getNameWitNumSupplier(){ // pay attention to the name!!!

   return $this->name.' ('.$this->NumSupplier.')';

}

and the use the CHtml::listData, like this:




return CHtml::listData(Town::model()->findAll(array(

                        'select'=>'town.id, town.name, count(supp.id) AS NumSupplier',

                        'join'=>'LEFT OUTER JOIN supplier supp ON supp.town = town.id',

                        'group'=>'town.id',

                        'alias'=>'town',

                        'order'=>'name',

                        'condition'=>'region=:region',

                        'params'=>array(':region'=>$_POST['supplier_region'])

                        )),

                'id', 'nameWitNumSupplier'); // again pay attention to the property!!!

I guess that with these changes it must work…

3

Thank you very much my friend! :)

Is it possible to turn that long query in to a relation? I tried doing this at first but I could not get it to work.

4

no problem!! ;)

5

How do I specify:


'join'=>'LEFT OUTER JOIN supplier supp ON supp.town = town.id'

If I want to create my query as a relation? Basically it’s telling me “Unknown column ‘supp.id’ in ‘field list’”