[Solid]

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.yiiframew...-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]

Dear Friend

I have 2 models

Block (id,name)

District(id,name)

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]

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]

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]

seenivasan, on 05 February 2013 - 09:48 AM, said:

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.

seenivasan, on 05 February 2013 - 09:48 AM, said:

Also check whether relation works by evoking the following code.

$model=Region::model()->find();
echo $model->country->name;

Regards.

error: Trying to get property of non-object

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

Thank you for helping.

[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);

[Solid]

Im getting the error:

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

on your last suggestion.

seenivasan, on 05 February 2013 - 11:35 AM, said:

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]

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]

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]

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]

Thank you!

That sloved it, I appriciate your help.

How can i mark this topic as sloved?