Listing 2 Models In A Chtml::listdata

Solid · February 5, 2013, 1:28pm

Hi, im trying to get a dropdownlist with something like the following structure:




<div class="select">

    <select name="Regions">

        <optgroup label="COUNTRY-NAME">

            <option value="id">REGION-NAME</option>

            <option value="id">REGION-NAME</option>

        </optgroup>

        <optgroup label="COUNTRY-NAME">

            <option value="id">REGION-NAME</option>

            <option value="id">REGION-NAME</option>

            <option value="id">REGION-NAME</option>

        </optgroup>

        .. and so forth ...

    </select>

</div>

My controller has this function:




public function listRegions()

    {

    	$list=array();

    	$models = Country::model()->findAll();

    	foreach($models as $model) {

    		$regions = Region::model()->findAllByAttributes(array('country'=>$model->id));

    		$regionList = CHtml::listData($regions, 'id', 'name'); 

    		$list[] = CHtml::listData($model->name, $regionList);

    	}

    	return $list;

    }

I’ve read http://www.yiiframework.com/wiki/48/by-example-chtml/ and googled around, but i cant seem to nail it.

I’ve allso tried these variations:




$list[] = CHtml::listData($model->id, $model->name, $regionList);




$list[] = CHtml::listData($models, $model->id, $model->name, $regionList);

Anyone see where i stepped wrong?

Thanks in advance.

seenivasan · February 5, 2013, 1:50pm

Dear Friend

I have 2 models

[b]Block (id,name)

District(id,name)[/b]

Block belong to a district.

Model Block.php has relation defined in the following way




public function relations()

	{


		return array(

			'district' => array(self::BELONGS_TO, 'District', 'd_id'),

		);

	}

Now I can make the dropDownList in the following way.




$data=CHtml::listData(Block::model()->findAll(),'id','name','district.name');//district.name is added as fourth parameter

echo CHtml::activeDropDownList($model,'name',$data);

Regards.

Solid · February 5, 2013, 2:40pm

Thank you for sharing.

I added the following to my MySQL:




ALTER TABLE  `index_region`

ADD FOREIGN KEY (`country`) REFERENCES  `decanvwp_main`.`index_country` (`id`) 

ON DELETE NO ACTION ON UPDATE NO ACTION ;

and generated a new model (Region, using the index_region table) and that had the following relations:


public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'country0' => array(self::BELONGS_TO, 'IndexCountry', 'Country'),

		);

	}

and i changed that to:


public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'country' => array(self::BELONGS_TO, 'Country', 'id'),

		);

	}

Now it does list all the regions, but not optgroup’s with the country names…

seenivasan · February 5, 2013, 2:48pm

Dear Friend

What Happens when you have the following code.




$model=new Region;

$data=CHtml::listData(Region::model()->findAll(),'id','name','country.name');

echo CHtml::activeDropDownList($model,'name',$data);

Also check whether relation works by evoking the following code.




$model=Region::model()->find();

echo $model->country->name;

Regards.

Solid · February 5, 2013, 2:57pm

seenivasan:

Dear Friend

What Happens when you have the following code.




$model=new Region;

$data=CHtml::listData(Region::model()->findAll(),'id','name','country.name');

echo CHtml::activeDropDownList($model,'name',$data);

It lists all the regions, without optgroups.

error: Trying to get property of non-object

As you understand, im not so familiar with database relations.

Thank you for helping.

seenivasan · February 5, 2013, 4:35pm

Dear Friend

Would you please try the following.




$model=new Region;

$data=CHtml::listData(Region::model()->findAll(),'id','name',function($model){ //passing the anonymous function as a parameter.

     return Country::model()->findByPk($model->country)->name;

});

echo CHtml::activeDropDownList($model,'name',$data);

Solid · February 8, 2013, 8:47pm

Im getting the error:


explode() expects parameter 2 to be string, object given

on your last suggestion.

seenivasan:

Dear Friend

Would you please try the following.




$model=new Region;

$data=CHtml::listData(Region::model()->findAll(),'id','name',function($model){ //passing the anonymous function as a parameter.

     return Country::model()->findByPk($model->country)->name;

});

echo CHtml::activeDropDownList($model,'name',$data);

But thanks for your effort anyway, seenivasan

And the first suggestion (i got the relations to work properly) just feeds in one country and one region after eachother till there is no more countrys left, and it just prints the rest of the regions in the last country(optgroup). And non of the regions belong to the country-optgroup they are in.

Anyone else care to help me with a suggestion? Im thinking i have to sort the data before making a list, like foreach Country, list regions…

luk1999 · February 8, 2013, 9:27pm

You should edit your relation in Country model and add ‘order’ parameter:




public function relations() {

  array(

    'regions' => array(self::HAS_MANY, 'Region', 'countryId', 'order' => 'name')

  );

}

Now (in controller) load data:




$countriesAndRegions = Country::model()->with('regions')->findAll(array('order' => 'name'));

$selectData = array();

foreach (countriesAndRegions as $country) {

  $selectData[$country->name] = CHtml::listData($country->regions, 'id', 'name');

}

This should works for these tables:




country:

id PK,

name VARCHAR


region:

id PK,

countryId FK to country

name VARCHAR

Solid · February 8, 2013, 9:39pm


CDbException


CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in order clause is ambiguous. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`flag` AS `t0_c2`, `Regions`.`id` AS `t1_c0`, `Regions`.`country` AS `t1_c1`, `Regions`.`name` AS `t1_c2` FROM `index_country` `t` LEFT OUTER JOIN `index_region` `Regions` ON (`Regions`.`country`=`t`.`id`) ORDER BY name

I feel like i should be able to troubleshoot alittle on my own, but i cant make any sense of this message…

Thanks for taking the time to help me, luk1999.

luk1999 · February 9, 2013, 11:47am

Both tables have column ‘name’, so you need to provide alias in order clause.

Model Country.php:


public function relations() {

  return array(

    'regions' => array(self::HAS_MANY, 'Region', 'countryId', 'alias' => 'r', 'order' => 'r.name')

  );

}

In contoller action:


$countriesAndRegions = Country::model()->with('regions')->findAll(array('order' => 't.name'));

$selectData = array();

foreach ($countriesAndRegions as $country) {

  $selectData[$country->name] = CHtml::listData($country->regions, 'id', 'name');

}

In view file:


echo CHtml::dropDownList('countriesAndRegions', null, $selectData);

This code will run a query:




SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `r`.`id` AS `t1_c0`, `r`.`countryId` AS `t1_c1`, `r`.`name` AS `t1_c2` 

FROM `a_country` `t` 

LEFT OUTER JOIN `a_region` `r` ON (`r`.`countryId`=`t`.`id`) 

ORDER BY t.name, r.name

Solid · February 9, 2013, 3:35pm

Thank you!

That sloved it, I appriciate your help.

How can i mark this topic as sloved?