Struggling With Basic Concepts

tomvdp · December 13, 2012, 1:14pm

I am new to the Yii framework and need some help regarding the best approach to handle a probably common problem.

In general terms, this is where I am stuck: an application shows editable data to the user that is not directly related to 1 database table. The data comes from multiple tables and, depending on the user input, one or more updates in different tables are necessary. Let us say the application also needs to update a status somewhere in some table and even add a record in yet another table. So we have simple input, but complex database updates and inserts.

What is the Yii approach for this ? Create an ActiveRecord model for every table that is involved in the updates/inserts, build the view based on the multiple models and then call save methods on all these models ? How do you get the dependencies correct: e.g. the newly created id of one record has to be used in the update in some other table. Can all this be done in a transaction so that if one fails all actions fail ?

For such cases, would it be a good solution to extend the CFormModel and add load & save methods to it ? I.e. build an “ActiveRecord light”: the extended CFormModel has all the benefits of validation rules and is extended so it can interact with the database. All specific business rules about loading/saving would be encapsulated in the load/save methods and would be written as a sequence of dbCommands with the model’s attributes available for the SQL parameters.

Am I right that the tight coupling of AR with a database table makes AR unfit for multi-table inserts/updates ? Is extending CFormModel the right path ? Or am I re-inventing the wheel here ?

Also, I am good in SQL and would want to use that skill. Leaving it to AR seems a waste.

Please guide this lost soul to the correct Yii-path!

dniznick · December 13, 2012, 7:21pm

Two pieces of ‘standard’ advice.

Read the Guide regarding form handling, models, controllers, active record.
Setup CRUD with Gii so you can see the default way to handle form submissions w/ model and controller.

To answer your specific question…

You are just making a multi-model form. You should create models for each DB table and create the proper relations between them.

In your controller, you create a model object for each type of form and assign to the view. Here is an example from one of my projects… the entities are a Template which has many criteria. In this case it is a many:many relation with a join table.

Controller:




public function actionCreate()

	{

		$model=new Template;


		if(isset($_POST['Template']))

		{

			$model->attributes=$_POST['Template'];

			if($model->save()) {	

				if(isset($_POST['Template']['criterias'])) {

					foreach ($_POST['Template']['criterias'] as $criteri) {

						//Foreach criteria posted to template, load TemplateCriteria model

						//and assign templateId and criteriaId, save it.

						$TemplateCriteria = new TemplateCriteria;

						$TemplateCriteria->templateId = $model->id;

						$TemplateCriteria->criteriaId = $criteri;

						$TemplateCriteria->save();

					}

				}

				//Set create flash message and send user to manage templates page

				Yii::app()->user->setFlash('success', "Template '$model->templateName' created successfully.");

				$this->redirect(array('index'));

			}

		}


		$this->render('create',array(

			'model'=>$model,

		));

	}

View _form.php:




<?php $form=$this->beginWidget('CActiveForm', array(

	'id'=>'template-form',

	'enableAjaxValidation'=>false,

)); ?>


	<p class="note">Fields with <span class="required">*</span> are required.</p>


	<?php echo $form->errorSummary($model); ?>


	<div class="row">

		<?php echo $form->labelEx($model,'templateName'); ?>

		<?php echo $form->textField($model,'templateName',array('size'=>50,'maxlength'=>50)); ?>

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

	</div>

 

<h5>Assign Criteria to Template:</h5>

<?php

$this->widget(

    'application.extensions.emultiselect.EMultiSelect',

    array(

		'sortable'=>true, 

		'searchable'=>true

	)

);


$a=Criteria::Model()->findAll(array('order'=>'criteriaName'));

$data = CHtml::listData($a, 'id', 'criteriaName'); //make list of criteria options indexed by id, displaying criteriaName

echo $form->DropDownList(

	$model,

	'criterias', //relation to M:M table in template model

	$data,

	array(

		'multiple' => 'multiple',

		'class' => 'multiselect',

		'style' => 'width: 800px;' //give this a label and move to css at some point

		)

	);

?>

Model 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(

			'criterias' => array(self::MANY_MANY, 'Criteria', 'template_criteria(templateId, criteriaId)'),

			'templateCriteria' => array(self::HAS_MANY, 'TemplateCriteria', 'templateId'),

			'assignments' => array(self::HAS_MANY, 'Assignment', 'templateId'),

		);

	}

[/code]

You will see in the controller code - when related data is posted it will come in as $_POST[‘Model’][‘relation’] and you can loop through it and do what you need to do. You can add as many related models as necessary.

As far as how to link dependencies (ids) - see the controller code where I load the JoinTable model. This should be enough to get you started.

tomvdp · December 14, 2012, 6:26pm

Thank you for your elaborate answer - very kind of you.

Believe it or not, I did read the guide and lots of other documents.

I understand your code and what it implies. It set me off to search for more information and I found this interesting article http://www.larryullman.com/2010/08/10/handling-related-models-in-yii-forms/ which (I presume) you have also read. I am surprised to see how much code is needed to update data coming from a multiselect. The code you showed is only for a "create" - an "update" is a bit more complex apparently.

And this is for the simple case where you have a page containing 1 "parent" model and "n child models". You save the parent, then loop over the children and save each in turn.

Would it be possible to wrap all the save methods in one transaction ?

Also, in your code you are not validating the children, so a parent could be written to the database without any children.

The problem where I am stuck is more complex than "a parent model with n identical related models".

I am trying to avoid a situation where a user has to go through some type of wizard when doing a registration. Instead he gets a page with many fields that can be edited. In the underlying database these are read from (& saved to) different tables. There are relations between the tables, there are statusses to be kept up-to-date, there are log records to be written etcetera.

Following a design logic with AR, that means 1 view with multiple models, then in the controller validating all models, saving/updating them in turn according to the business rules.

I am tempted to go for an extended FormModel for this particular "difficult screen" of the application:

manually define all attributes that are presented to the user;
add load / save functions that run update & insert SQL statements inside a transaction;
same validation rules as for a solution with AR, except they are all in one place;
same view as for a solution with AR.

Maintenance cost will be higher because there is no metadata coming from the database, so each change to the underlying tables might have an impact on the code.

On the other hand, it will be much more efficient and everything runs in a transaction.

dniznick · December 15, 2012, 5:26pm

Well. There is no reason why you can’t use a transaction with the code I gave. I have another piece where the related children are required and there is an extension for that if you want to shorten your code.

Of course you can also use an extension to save related records more easily but understand they are doing essentially what Larry’s examples show.