dependent dropdown

Hi all,

i’m new with yii - it looks really great.

I followed the cookbook to create dependent dropdown selects. Everthings works fine when creating new entries but the selects left their value when updating existing entries (using the standard crud actions). Existing values are not marked as selected.

Is there any common practice to handle this issue?

Kind regards

Tom

Some more explanation.

There is a hierarchy of 3 dropdown selects (company, project, order) and a bill should be created for one specific order. The bill model does only contain the order_id.

The first both selects (company and project) are created via


<?php echo CHtml::dropDownList('company_id','', CHtml::listData(Company::model()->findAll(), 'id', 'name')

the third one with


<?php echo $form->dropDownList($model,'order_id', array()); ?>

The form i used is CActiveForm.

As far as i’m new with yii: Is there a better way to doe this?

I remember this problem was already discussed on the forum… have you tried to search for this?

Hi, thanks for your reply. Yes, I found a few posts covering this szenario. But this posts does not contain a solution or hint how to handle the update.

  1. Level: Company with id an name

  2. Level: Project with id, company_id and name

  3. Level: Order with id, project_id and name

To be created: Bill with id, project_id, …

The model “bill” only contains the project_id. I can’t use


$form->dropDownList($model, 'company_id' or 'project_id' 

because company_id and project_id are not part of the model. Using


echo CHtml::dropDownList('company_id'

i can populate data to the dropdown. But since there is no company_id in the model bill i have no idea on how to populate data when using the update / editAction.

Is is possible to use model relations to introduce "company_id" and "project_id" to the model bill? Is this the correct way to handle the szenario?

Thank you very much,

Tom

You can use a bit of CDbCriteria.

Ther first dropdown is free.

The second dropDown will be populated using something like:


Project::model()->findAll('company_id= :company', array(':company'=>$model->company))

Of corse only if $model->company is set.

The third will be something like:


Order::model()->findAll('project_id= :project', array(':project'=>$model->project))

You can create needed property in the model and give them the needed values…

But this will result in a very poor data model. The hierarchy is company->project->order->bill and there is no need or sense to store company_id and project_id in the bill.

Or did i misunderstand your post?

Certainly i have explaned the problem not clear enough. Sorry.

When creating a new bill the user should (using one form)first select the dropdown company (e.g. cx), project (e.g. py) and order (e.g. oz) and enter the billing details. This all works fine.

When editing an existing bill the dropdowns for company, projekt and order should have cx, py and oz as selected values.

If the first dropdown ist free: How could i get the correct option (cx) to be the selected value?

Thank you very much,

Tom

I’m puzzled now… you wrote that in the bill model you don’t have the company_id and product_id… but to enter the bill details you ask to choose a company and a project (and order)… so your bill model (and table) must have those fields where you will store the company, project and order id’s… right?

Sorry for my vague specification.

Looking to the ER data model the bill does only have a relations to the order. The orders have a relation to a project and projects have a relation to companies.

May be i’m to much focusing to the ER model and do not understand who to put the relations together in one yii model?

The ER is fine… so in the bill model you have only the order_id…

To get the values entered/selected for the company_id and product_id you need to make new attributes in the bill model…

Thank you for your patience

That sounds good. Can you please provide some details on how to define this new attributes?

In your bill model at the top you just add




public company_id;

public order_id;



Then add some validation rule for them… or at least set them as safe… you can even add them to attributeLabels() if you need a label for them…

In the bill model you use them like any other model attribute… like: $model->company_id

Thank you very much.

I have set up a complete new example. This took a while. The follwing tables are use. The models created seams to contain the correct relation.




CREATE TABLE IF NOT EXISTS company (

  id int(11) NOT NULL AUTO_INCREMENT,

  name varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


CREATE TABLE IF NOT EXISTS project (

  id int(11) NOT NULL AUTO_INCREMENT,

  company_id int(11) NOT NULL

	COMMENT "CONSTRAINT FOREIGN KEY (company_id) REFERENCES company(id)",

  name varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


CREATE TABLE IF NOT EXISTS task (

  id int(11) NOT NULL AUTO_INCREMENT,

  project_id int(11) NOT NULL

	COMMENT "CONSTRAINT FOREIGN KEY (project_id) REFERENCES project(id)",

  name varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


CREATE TABLE IF NOT EXISTS bill (

  id int(11) NOT NULL AUTO_INCREMENT,

  task_id int(11) NOT NULL

	COMMENT "CONSTRAINT FOREIGN KEY (task_id) REFERENCES task(id)",

  foo varchar(64) NOT NULL,

  PRIMARY KEY (id)

);



Now i have tried to modify the bill controler




$model=$this->loadModel($id);



to load the related data (task, projekt, company. As far as there is no documentation in the class reference for loadModel this took a while again :slight_smile:

Reading the tutorials again it looks like that


$model=Bill::model()->with('task', 'task.project', 'task.project.company')->findByPk((int)$id);

might be the correct way.

But $model->company is empty / not known. $model->task->project->company is not empty.

Any hint on how to proceed with this to get the dropdown values are selected is very welcome…

Regards

Tom

Thank you very much. The combination of both replies (mdomba and zaccaria) was very helpful.

The bill controller




$model=Bill::model()->with('task', 'task.project', 'task.project.company')->findByPk((int)$id);

$model->project_id = $model->task->project_id;

$model->company_id = $model->task->project->company_id;



In the view




<?php echo $form->dropDownList($model,'company_id', CHtml::listData(Company::model()->findAll(), 'id', 'name'), array(

    'ajax' => array(

    'type'=>'POST',

    'url'=>CController::createUrl('bill/getProjects'),	

    'update'=>'#'.CHtml::activeId($model,'project_id')))); 

?>


<?php echo $form->dropDownList($model,'project_id', CHtml::listData(Project::model()->findAll('company_id= :company', array(':company'=>$model->company_id)), 'id', 'name'), array(

    'ajax' => array(

    'type'=>'POST',

    'url'=>CController::createUrl('bill/getTasks'),	

    'update'=>'#'.CHtml::activeId($model,'task_id')))); 

?>


<?php echo $form->dropDownList($model,'task_id', CHtml::listData(Task::model()->findAll('project_id= :project', array(':project'=>$model->project_id)), 'id', 'name')); ?>



Thank you very much again - i have learned a lot.

One small issue is left. If company dropdown is changed the projects dropdown is updated by the ajax request. But this update does not perform the next ajax request to update the tasks (orders) dropdown list.

As far as the jquery code in the html page is generated by gii - is there a common way to chain these update requests?

Kind regards,

Tom

If found that ‘ajax’=>array(…) just wraps jQuery.ajax(). So i can attach a callback function to the complete-handler which triggers the change event to the releated dropdown.

My issue is completed solved - thank you very much again.

Tom,

Would you be able to post the code for that please? I have a similar problem and cannot seem to work it out (I am a very new to PHP and Yii).

While I am in this thread I might as well ask my other question. I got the dependent dropdowns to work, but now it seems I cannot save changes from dropdowns. If I change a text field, no problem, but changing a dropdown does not get saved. I can post my code if needed, but was hoping there was someghing simple someone has seen before.

Thanks.

I have created a small example. Please find attached this project. You may unzip the project to your htdocs directory. I have also started to write a little “how to” to explain it step by step. Currently i’am looking for a native speaker to review my “germenglish”.

You have to create a database with the following tables and data to use the example.




CREATE TABLE IF NOT EXISTS company (

  id int(11) NOT NULL AUTO_INCREMENT,

  name varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


CREATE TABLE IF NOT EXISTS project (

  id int(11) NOT NULL AUTO_INCREMENT,

  company_id int(11) NOT NULL

	COMMENT "CONSTRAINT FOREIGN KEY (company_id) REFERENCES company(id)",

  name varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


CREATE TABLE IF NOT EXISTS task (

  id int(11) NOT NULL AUTO_INCREMENT,

  project_id int(11) NOT NULL

	COMMENT "CONSTRAINT FOREIGN KEY (project_id) REFERENCES project(id)",

  name varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


CREATE TABLE IF NOT EXISTS bill (

  id int(11) NOT NULL AUTO_INCREMENT,

  task_id int(11) NOT NULL

	COMMENT "CONSTRAINT FOREIGN KEY (task_id) REFERENES task(id)",

  foo varchar(64) NOT NULL,

  PRIMARY KEY (id)

);


INSERT INTO company (id, name) VALUES

(1, 'Company 1'),

(2, 'Company 2');


INSERT INTO project (id, company_id, name) VALUES

(1, 1, 'Company 1 Project 1'),

(2, 1, 'Company 1 Project 2'),

(3, 1, 'Company 1 Project 3'),

(4, 2, 'Company 2 Project 1'),

(5, 2, 'Company 2 Project 2'),

(6, 2, 'Company 2 Project 3');


INSERT INTO task (project_id, name) VALUES

(1, 'Company 1 Projekt 1 Order 1'),

(1, 'Company 1 Projekt 1 Order 2'),

(2, 'Company 1 Projekt 2 Order 1'),

(2, 'Company 1 Projekt 2 Order 2'),

(3, 'Company 1 Projekt 3 Order 1'),

(3, 'Company 1 Projekt 3 Order 2'),

(4, 'Company 2 Projekt 1 Order 1'),

(4, 'Company 2 Projekt 1 Order 2'),

(5, 'Company 2 Projekt 2 Order 1'),

(5, 'Company 2 Projekt 2 Order 2'),

(6, 'Company 2 Projekt 3 Order 1'),

(6, 'Company 2 Projekt 3 Order 2');



Wow, that’s amazing. I certinaly didn’t expect such a response. I look forward to examining your sample code.

Thanks very much!

The code is good… but when run there is no link on the main page that goes to "bill" controller… you need to type in the URL


localhost/extdd/index.php/bill

and then go to create new bill to see this working…

And there is a slight problem… on initial display the two dependant dropdown are empty… and you need to change the company so that the other two dropdowns gets filled…