Dependent Dropdownlist For Indirectly Related Table

Yii 1.1 General Discussion
1

Hello Everyone!

I am fairly new to the yii framework and I’ve been wanting to do certain things in my project the “yii” way.

I’ve encountered a problem where I need a dependent dropdown list for a secondly related table. There is a diagram of the related tables in attachment. Please see it to fully understand the problem.

4194

companySector.bmp

So I have the following tables:

[b]Sector

Company

CompanySector

Employee[/b]

And the following relations:

Sector belongs to CompanySector

Company belongs to CompanySector

CompanySector belongs to Employee

Now, for each company there is a list of sectors available for that company (this data is available in CompanySector).

When a new employee is being inserted, I need to specify in which company and sector the employee belongs to. To do this, I want a dropdown list that will display all the companies. Upon selecting a company, the list of all available sectors for that company should be displayed in another dropdown list.

Then, when both company and sector are selected, I will make a query that will find the primary key of the CompanySector record where ID_Company and ID_Sector are the values from the dropdown lists (and then insert it in F_ID_CompanySector in Employee). I need to make the dependent dropdownlists first however.

I tried this article but i had no luck in solving my problem.

I added the following variables in my Employee model:




public $ID_Company;

public $ID_Sector;

I have the following function in the Company model which lists all values for the company (used in dropdown list):




        public function getCompanyOptions()

        {

            return CHtml::listData(Company::model()->findAll(), 

                    'ID_Company', 'Company_Name');

        }

And i have the following in the _from of Employee for the company and sector dropdowns




                <td><?php echo $form->labelEx($model,'ID_Company'); ?></td>

                <td>

                    <?php echo $form->dropDownList($model,'ID_Company',

                Company::model()->getCompanyOptions(), array('prompt' => 'Select Company')); ?>

                    <?php echo $form->error($model,'ID_Company'); ?>                    

                </td>

                

                <td><?php echo $form->labelEx($model,'ID_Sector'); ?></td>

                <td>

                    <?php echo $form->dropDownList($model,'ID_Sector',

                Sector::model()->getSectorOptions(), array('prompt' => 'Select Sector')); ?>

                    <?php echo $form->error($model,'ID_Sector'); ?>                       

                </td>

If you need any more information please let me know.

Thank you for your time.

2

I see that in your _form you haven’t implemented what wrote in

http://www.yiiframework.com/wiki/24/

Try to paste code with that implementation, so can found the problem.

3

I have done that, but I erased it since it didn’t work.

4

Post your code that don’t work, otherwise it’s difficult debug your error.

5

This is the _form




<!-- Company -->

                <td><?php echo $form->labelEx($model,'ID_Company'); ?></td>

                <td>

                    <?php echo $form->dropDownList($model,'ID_Company',

                Company::model()->getCompanyOptions(),

                            array(

                                'prompt' => 'Select Company',

                                'ajax' => array(

                                'type'=>'POST',

                                'url'=>CController::createUrl('Employee/updateSectors'), 

                                'dataType'=>'json',

                                'data'=>array('ID_Company'=>'js:this.value'),  

                                'success'=>'function(data) {

                                    $("#ID_Sector").html(data.dropDownSectors);

                                }',)

                                )); ?>

                    <?php echo $form->error($model,'ID_Company'); ?>                    

                </td>

                

                <!-- Sector -->

                <td><?php echo $form->labelEx($model,'ID_Sector'); ?></td>

                <td>

                    <?php echo $form->dropDownList($model,'ID_Sector',

                array(),array('prompt' => 'Select Sector')); ?>

                    <?php echo $form->error($model,'ID_Sector'); ?>                       

                </td>

This is the EmployeeController:




        public function actionUpdateSectors()

        {

                //Sectors

                $data = Sector::model()->findAll('F_ID_Company=:F_ID_Company', array(':F_ID_Company'=>(int) $_POST['ID_Company']));

                $data = CHtml::listData($data,'ID_Sector','Sector_Name');

                $dropDownSectors = "<option value=''>Select Sector</option>"; 

                foreach($data as $value=>$name)

                    $dropDownSectors .= CHtml::tag('option', array('value'=>$value),CHtml::encode($name),true);


                // return data (JSON formatted)

                echo CJSON::encode(array(

                  'dropDownSectors'=>$dropDownSectors,

                ));

        }
6

The point is that the tables Sector and Company are not directly connected to table Employee. They are first connected to table CompanySector and then CompanySector connects to Employee.

7

Then:




                            array(

                                'prompt' => 'Select Company',

                                'ajax' => array(

                                'type'=>'POST',

                                'url'=>CController::createUrl('Employee/updateSectors'), 

                                'dataType'=>'json',

                                 'data'=>array('ID_Company'=>'js:this.value'),         

                                'success'=>'function(data) {

                                    $("#ID_Sector").html(data.dropDownSectors);

                                }',)

                                )); ?>

In your code, at ‘data’ key value, you set ID_Company instead in Controller’s action you get ID_Sector.

Rename key or in the _form or in the controller.

8

Fabrizio Caldarelli, thank you very much for your time and your quick replies. I managed to solve my problem.

Here is the solution in case anyone else encountered the same problem:

_form:




                <!-- Company -->

                <td><?php echo $form->labelEx($model,'ID_Company'); ?></td>

                <td>

                    <?php echo $form->dropDownList($model,'ID_Company',

                Company::model()->getCompanyOptions(),

                            array(

                                'prompt' => 'Select Company',

                                'ajax' => array(

                                'type'=>'POST', 

                                'url'=>CController::createUrl('updateSectors'),

                                'update'=>'#Employee_ID_Sector', 

                                'data'=>array('Employee_ID_Company'=>'js:this.value'),

                                )

                                )); ?>

                    <?php echo $form->error($model,'ID_Company'); ?>                    

                </td>

                

                <!-- Sector -->

                <td><?php echo $form->labelEx($model,'ID_Sector'); ?></td>

                <td>

                    <?php echo $form->dropDownList($model,'ID_Sector',

                array(),array('prompt' => 'Select a Company First')); ?>

                    <?php echo $form->error($model,'ID_Sector'); ?>                       

                </td>

EmployeeController:




        public function actionUpdateSectors()

        {

            $companyID=$_POST['Employee_ID_Company'];

            

            if(empty($companyID))

            {

                echo "<option value=''>Select a Company First</option>";

            }

            else

            {

                $findSectors = Yii::app()->db->createCommand()

                    ->select('ID_Sector, Sector_Name')

                    ->from('company_sector, company, sector')

                    ->where('F_ID_Company=ID_Company AND F_ID_Sector=ID_Sector')

                    ->andWhere('ID_Company='.$companyID)

                    ->queryAll();


                $data=CHtml::listData($findSectors,'ID_Sector','Sector_Name');


                echo "<option value=''>Select a Sector</option>";

                foreach($data as $value=>$sectorName)

                echo CHtml::tag('option', array('value'=>$value),CHtml::encode($sectorName),true);

            }

        }

What actually was making problems:

When using this


<?php echo $form->dropDownList($model,'ID_Company',

the ID of the dropdown is Employee_ID_Company by default, not ID_Company. The same goes with the every other form component. Moreover, I forgot to add the action in the accessRules (how could i :rolleyes: ):


'actions'=>array('index','view', 'updateSectors'),

Now I need help for one more thing…

To make things a bit more complicated, I want the sector dropdown to be hidden, and then to show when I choose a company. How can I achieve this the "yii" way?

Thank you once again.