Yii Relational Active Record Tutorial

Yii website has an excellent written document on relational active record. I was confused at first with the example and some of the terms Yii used for their relational active record that really cause me to waste some time on this section. Therefore i decided to write a quick and dirty tutorial on Yii relational active record hoping it will post some benefits for those who are still learning Yii framework.

Yii Relational Active Record Approaches

In Yii there are two approaches mainly the lazy loading approach and the eager loading approach. Both approaches have been documented on Yii relational active record tutorial. But if you are lazy to read, this is how each approach is being use. Lazy loading approach is use when you are dealing with 1 record and eager loading approach comes in handy when there are more than 1 records you wish to access. This is made in this way to reduce the number of join which lead to inefficiency according to Yii document page

Setup Relational Active Record

In order to get relational active record to work, we have to setup the relationship between each model. Assuming we have two table, Invoice and InvoiceItem tables as shown below,

CREATE TABLE IF NOT EXISTS `invoice` (
	`invoiceId` MEDIUMINT UNSIGNED NOT NULL,
	`invoiceTotalAmount` DOUBLE NOT NULL,
	PRIMARY KEY (`invoiceId`)
) 

CREATE TABLE IF NOT EXISTS `invoice_item` (
	`invoiceItemId` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`invoiceItemLineId` MEDIUMINT UNSIGNED NOT NULL,
	`invoiceId` MEDIUMINT UNSIGNED NOT NULL COMMENT "CONSTRAINT FOREIGN KEY (invoiceId) REFERENCES be_invoice(invoiceId)",
	PRIMARY KEY (`invoiceItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now, if we use Gii to create our models, we will get 2 models, InvoiceItem and Invoice class model. Now we will need to overwrite the method 'relations' in Invoice class model in order to retrieve Invoice and its items into the same page. In this case, using relational active record is the best option we have. In order to use relational active record, we have to modified the Invoice class model method, 'relations'. Hence, in this method we will declare the following relationship with InvoiceItem.

	/**
	 * @return array relational rules.
	 */
	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(
			'invoiceItems' => array(self::HAS_MANY, 'InvoiceItem', 'invoiceId', 'together'=>true ),
		);
	}

The above means that i have a 1 to many relationship with InvoiceItem class that share the same key called 'invoiceid' and i will name my relationship 'invoiceitems'. Usually here is the most confusing part for everyone to pick up (to setup a relationship). The format for this relationship can be seen on Yii documentation too but i will show it here just for your conveniences.

'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)

In layman term, my relationship means "My invoice class model has a one to many relationship with InvoiceItem class model and we are tie together with our primary and foreign key which in this case foreign key is InvoiceItems". Hope it helps anyone stucked here to understand how a relationship can be initialized. Let's continue.

Getting The Relational Active Record

Now, we can get our counterpart data after we have setup our relational active record. In my case, I'm confused and stucked here since i am not very familiar with the example given in the document. Anyway, we can now utilized the two approaches mentioned previously to retrieve our relational active records. I will first introduce the lazy loading approach.

Lazy Loading Approach

Please bear in mind that the lazy loading approach will not automatically populate the wanted data to you. You will have to initialize it. And this is the mindset i had and got stuck with because i always though the "lazy" means i will not have to initialize it. Anyhow, assuming you have your crud setup for your invoice table. In this case, there should be a controller called 'InvoiceController.php'. In my example, i want my data for both table to appear on my create invoice page. In order to do that, i will have to go to the view folder and open up '_form.php' where the structure is being made as shown below.

<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(
	'id'=>'invoice-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,'invoiceId'); ?>
		<?php echo $form->textField($model,'invoiceId'); ?>
		<?php echo $form->error($model,'invoiceId'); ?>
	</div>
	
	<div class="row">
		<?php echo $form->labelEx($model,'invoiceTotalAmount'); ?>
		<?php echo $form->textField($model,'invoiceTotalAmount'); ?>
		<?php echo $form->error($model,'invoiceTotalAmount'); ?>
	</div>
</div>

Now, we will need to add in the lazy loading approach method.

$items=$model->invoiceItems;
foreach($items as $item){
	echo $item->invoiceItemLineId . "<br/>";
}

Take note that the invoiceItems is my relationship name that i have declared in my relations method on the model. You can also do it this way which is shown on Yii website.

// retrieve a record object
$invoice=Invoice::model()->findByPk(1);
// invoiceItems is the relationship name i have declared
$author=$invoice->invoiceItems;

Once we put this into the _forms structure file, we will have this.

<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(
	'id'=>'invoice-form',
	'enableAjaxValidation'=>false,
)); 
$items=$model->invoiceItems;
foreach($items as $item){
	echo $item->invoiceItemLineId . "<br/>";
}
?>

	<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,'invoiceId'); ?>
		<?php echo $form->textField($model,'invoiceId'); ?>
		<?php echo $form->error($model,'invoiceId'); ?>
	</div>
	
	<div class="row">
		<?php echo $form->labelEx($model,'invoiceTotalAmount'); ?>
		<?php echo $form->textField($model,'invoiceTotalAmount'); ?>
		<?php echo $form->error($model,'invoiceTotalAmount'); ?>
	</div>
</div>

the above should just print out the line number of the items to show you how many items are available in this invoice.

Eager Loading Approach

Eager loading approach will be much MUCH easier. This can be easily figured just by reading what is written on Yii website. The eager loading approach required the word 'with' to join the 2 table together. A simple example which you can is shown below,

$invoices = Invoice::model()->with('invoiceItems')->findAll();

By the way, you can use this sentence anywhere as long as you need the data. What the above is saying in layman term is that "Using the Invoice class model, we fire up model to initial the db call and use the relationship called invoiceItems and show all results to me".

Summary

Hopefully this tutorial can help further explain what has already existed in the Yii documentation for anyone to get the hang of Yii relational active record. The tutorial here is pretty simple and high level. Hopefully to solve starters on Yii on issues on Yii relational active record.

4 thoughts on “Yii Relational Active Record Tutorial

  1. Thanks for your tutorial; it's what I was looking for. However, I can't get the _view to put out the line items as indicated.
    This is my code for that file:

    beginWidget('CActiveForm', array(
    'id'=>'invoice-form',
    'enableAjaxValidation'=>false,
    ));
    $items=$model->invoiceItems;
    foreach($items as $item){
    echo $item->invoiceItemLineId . "";
    }
    ?>

    Fields with * are required.

    errorSummary($model); ?>

    labelEx($model,'invoiceId'); ?>
    textField($model,'invoiceId'); ?>
    error($model,'invoiceId'); ?>

    labelEx($model,'invoiceTotalAmount'); ?>
    textField($model,'invoiceTotalAmount'); ?>
    error($model,'invoiceTotalAmount'); ?>

    isNewRecord ? 'Create' : 'Save'); ?>

    endWidget(); ?>

    <!-- form -->

    Thanks again

  2. Sorry, the beginning of the file was clipped:

    beginWidget('CActiveForm', array(
    'id'=>'invoice-form',
    'enableAjaxValidation'=>false,
    ));
    $items=$model->invoiceItems;
    foreach($items as $item){
    echo $item->invoiceItemLineId . "";
    }
    ?>
    ....

  3. _view? i reckon it must have something to do with your controller not passing in the correct information to the view 🙂

Comments are closed.