Nested Clistview From Two Data Sources

Yii 1.1 General Discussion
1

Hi all,

I’m pretty new to Yii an am a bit stuck at the moment. I do have two database tables. One with categories and another one with different items. What I want to achieve is a view similar to the following:

  • data from category table (title, text, etc.)

    • entry from item table

    • another entry from item table

  • data from category table (title, text)

    • entry from item table

    • another entry from item table

So I was thinking along the lines of specifying an actionIndex in the controller with two CActiveDataProvider for the two tables. And then use the two providers in the respective views in a CListView (first goes into index.php and the second into _view.php). That does not seem to work and I’m not sure if that’s even the best approach.

Controller ActionIndex:




public function actionIndex() {

    $simulation=new CActiveDataProvider('Category');

    $simUrls=new CActiveDataProvider('Items');

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

       'Category'=>$category,

       'Items'=>$items,

    )); }

View index.php




<?php $this->widget('zii.widgets.CListView', array(

    'dataProvider'=>$category,

    'itemView'=>'_view',

)); ?>

_view.php




<?php

$box = $this->beginWidget('bootstrap.widgets.TbBox', array(

    'title' => CHtml::encode($data->title). ' ('.CHtml::encode($data->cve).')',

));

?>


==>> Here would be the code for the items from the second table...


<?php $this->endWidget();?>

Any insight or hint into the right direction would be much appreciated.

Many thanks,

Hugo

2

Hi Hugo

This is just a quick example. Not tested.

It uses ‘eager loading’.

Eager loading means: all the needed data is obtained

in the category_model, which will include a function

looking something like this:


public function listViewData()

{

	$criteria=new CDbCriteria;

	$sort = new CSort;


	/* Filter the category records here to only include the wanted records */

	...

	   

	/* Get items for each category loading */

	$criteria->with = array(

		'relation_from_category_to_items' => array('select'=>'item_name'),

	);

	$criteria->together = true;

	

	/* Sort records */

	$sort->defaultOrder = array(

		'category_name' => CSort::SORT_ASC,

	);

	

	return new CActiveDataProvider($this, array(

		'pagination'=>array(

			'pageSize'=> 20,

		),

		'criteria'=>$criteria,

		'sort'=>$sort,

	));

}

Your controller will render view_a, which looks like this:


<div class="solid_border">

	<?php $this->widget('zii.widgets.CListView', array(

		'dataProvider'=>$category_model->listViewData(),

		'itemView'=>'//aut6/view_b',

	)); ?>

</div>

view_a will print EACH category (and its items) using view_b.

(For each new category, view_b will be printed.)

view_b will look something like this:


<!-- Print category label and value --> 

<div class="row-fluid">

	<div class="span3 heading1">

		<?php echo CHtml::encode($data->getAttributeLabel('category_name')); ?>:

	</div>

	<div class="span9">

		<?php echo CHtml::encode($data->category_name); ?>

	</div>

</div>


<!-- Print items' label as a heading -->

<div class="row-fluid">

	<div class="span3 heading2">

		<u><?php echo CHtml::activeLabel($data,'relation_from_category_to_items.item_name');?></u>

	</div>

</div>


<!-- Print item values -->

<?php foreach ($data->relation_from_category_to_items as $child):?>

	<div class="row-fluid">

		<?php echo $child->item_name; ?>

	</div>	

<?php endforeach; ?>

Warning about ‘eager loading’:

Having just a category and item table is simple enough. But if a list of available category names and item names are stored in other tables, or if you need to get data from other tables for whatever reason, then eager loading can become quite tricky to do.

And you can easily skip records without knowing it. For example, let’s take the following situation:

Select all categories; that have items; of which more than 5 were sold last year.

Now, if there is a category with no items; or with items but only 3 were sold last year, then that category will not be included in the list. And you don’t know if your user will even notice it.

There are ways to ensure that all categories are included, but as I said, it can become tricky.

For this reason, when doing printouts at least, I prefer lazy loading. With lazy loading your category_model’s listViewData() function will only load the categories. That way you are sure to get ALL categories. Then in view_b, you can retrieve the items’ data for the individual categories. Here you can use the relation_from_category_to_items; or you can use a similar function in the item_model to retrieve records related to the category.

Hope this helps.