How To Displaying Relational Tables For Report

i’ve got some problem while displaying relational data from 2 table or more, what i expect for the result is something like the image that i attach 3712

display-relational-tables_6.png
here/below, i wonder what should i do to my app in order to displaying the report like this,?

i really need to know how to do this on my yiiapp,

best regards for all the responses,

many thanks,

Dear Friend

Let us consider an example.

We have table author and post.

One author has many posts through auth_id(FK).

We are displaying author’s profle(CDetailView) and posts made by author(CGridView) in a single page.

view.php




<h1>View Author <?php echo $model->id; ?></h1>


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

	'data'=>$model,

	'attributes'=>array(

		'id',

		'name',

	),

)); ?>


<h4 style="margin-top:10px;"><?php echo "Posts made by ".$model->name;?></h4>


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

	'id'=>'post-grid',

	'dataProvider'=>new CArrayDataProvider($model->posts),//Using CArrayDataProvider

	'columns'=>array(

		'id',

		'title',

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>




If we are using CActiveDataProvider then we have to declare the $dataProvider in the following way.




'dataProvider'=>new CActiveDataProvider("Post",array

        (

	'criteria'=>array('condition'=>'t.auth_id='.$model->id)

	)

  ),



I hope this helps you some way.

Regards.

thanks seenivasan, :)

but i can’t find the usage of CArrayDataProvider,this is my first time using yii,

where should i declare the $DataProvider variable?

what is the "t" function in "t.auth_id", where is it related to?

while i use the code in my view,

i got an error referencing this line :




'criteria'=>array('condition'=>'t.auth_id='.$model->id)



Dear FRIEND

I am sorry about that.

It is working in my localhost.

Nothing special about "t", it is table alias given by YII when performing query.

You can take that away.

Now kindly check the following.




'dataProvider'=>new CActiveDataProvider("Post",array

        (

        'criteria'=>array(

                   'condition'=>'auth_id=:id',

                    'params'=>array(":id"=>$model->id),

                        ),

        

        )

  ),



Regards.

thx seenivasan,

how about this line,




'dataProvider'=>new CActiveDataProvider("Post",array

did “Post” the name of ‘posts’ table model or it was a method from yii?

what was the function?

and also :

what was the function of ‘post-grid’?




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

        'id'=>'post-grid',



bro seenivasan, i got my real problem here : :(

on my table :

invoice

PK --> invoice_id

attr : reserveNumber ,tableNumber, totalBill

food

PK --> food_id

FK --> invoice_id

attr : foodName, fdqty, fdprice, fddiscount

drink

PK --> drink_id

FK --> invoice_id

attr : drinkName, drqty, drprice, drdiscount

is it possible to get all the list of food and drink in one view

using invoice view?

thanks for replaying,

i really got interested in yii, and

sorry for giving you a lot of question,

regards, ryan :)

Dear Friend




'dataProvider'=>new CActiveDataProvider("Post",array

        (

        'criteria'=>array(

                   'condition'=>'auth_id=:id',

                    'params'=>array(":id"=>$model->id),

                        ),       

        )

  ),



In the above constructor function of CActiveDataProvider

1."Post" means class Post. If second parameter is empty it is going to fetch all the rows from table post.

  1. If you give some criteria in second parameter, data fetched would be in accordance with supplied criteria.



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

        'id'=>'post-grid',



In above mentioned code, id denotes the html attribute id for CGrid.

All ajax functionalities revolve around this id.

It is imperative that if a page contains more than one widgets, they should be given unique id to avoid errors.

I presume that one invoice HAS_MANY food and HAS_MANY drink.




public function relations()

	{

		return array(

			'foods' => array(self::HAS_MANY, 'Food', 'invoice_id'),

                        'drinks' => array(self::HAS_MANY, 'Drink', 'invoice_id'),

		);

               

	}







$invoice=Invoice::model()->findByPk(1);


$invoice->foods;// All the food objects related to invoice1.

$invoice->drinks://All the drinks related to invoice1.



Then below the invoice view we can display them as Grids.

Regards.

great explanation bro,

unfortunately me , i still found many error,

my invoice/view.php script :


 $this->widget('zii.widgets.grid.CGridView', array(

        'id'=>'invoice-grid',

		'dataProvider'=>new CActiveDataProvider("Post",array

        (

        'criteria'=>array(

                   'condition'=>'invoice_id=:id',

                    'params'=>array(":id"=>$model->invoice_id),

                        ),       

        )

  ),

        'columns'=>array(

                'idSpekLokasi',

				'namaSpek',

				'ketSpek',

                //'title',

                array(

                'class'=>'CButtonColumn',            ),

        ),

));



my controller looks :




public function actionView($id)

	{

		$invoice = Invoice::model()->findByPk($id);

		$invoice ->foods;

		$invoice ->drinks;

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

          'invoice' => $invoice,                      

        ));


		//$this->render('view',array(

		//'model'=>$this->loadModel($id),

		//));

	}




honestly i really doubt about this part,

  1. is it right to put the code under the actionView()?

  2. did the code really like these?

and the relations() in my invoice model = Invoice.php




'foods' => array(self::HAS_MANY, 'Invoice', 'invoice_id'),

'drinks' => array(self::HAS_MANY, 'Invoice','invoice_id'),



the error was

" Please specify the "data" property. "


      * @var string the URL of the CSS file used by this detail view. Defaults to null, meaning using the integrated

126      * CSS file. If this is set false, you are responsible to explicitly include the necessary CSS file in your page.

127      */

128     public $cssFile;

129 

130     /**

131      * Initializes the detail view.

132      * This method will initialize required property values.

133      */

134     public function init()

135     {

136         if($this->data===null)

137             throw new CException(Yii::t('zii','Please specify the "data" property.'));

138         if($this->attributes===null)

139         {

140             if($this->data instanceof CModel)

141                 $this->attributes=$this->data->attributeNames();

142             else if(is_array($this->data))

143                 $this->attributes=array_keys($this->data);

144             else

145                 throw new CException(Yii::t('zii','Please specify the "attributes" property.'));

146         }

147         if($this->nullDisplay===null)

148             $this->nullDisplay='<span class="null">'.Yii::t('zii','Not set').'</span>';

149         $this->htmlOptions['id']=$this->getId();

and when i changed the actionView() controller into default, i got this error :

"include(Post.php): failed to open stream: No such file or directory "

i wonder that where was the mistake that i’ve made,

do you have any solution for such condition bro? i really wonder to know…

my best regards bro,

thanks. :)

Dear Friend




public function actionView($id)

{   

    $this->loadModel($id);


    $foodProvider=new CActiveDataProvider("Food",array //not "Post" Post is my example.

        (

        'criteria'=>array(

                   'condition'=>'invoice_id=:id',

                    'params'=>array(":id"=>$model->invoice_id),

                        ),       

        ));


       $drinkProvider=new CActiveDataProvider("Drink",array

        (

        'criteria'=>array(

                   'condition'=>'invoice_id=:id',

                    'params'=>array(":id"=>$model->invoice_id),

                        ),       

        ));	

	

      $this->render('view',array('model'=>$model,'foodProvider'=>$foodProvider,'drinkProvider'=>$drinkProvider)));

}



view.php may be looking like this.




<h1>View Invoice #<?php echo $model->id; ?></h1>


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

	'data'=>$model,

	'attributes'=>array(

		'invoice_id',

		'reserveNumber',

                 'tableNumber', 

                  'totalBill'

	),

)); ?>


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

        'id'=>'food-grid',

        'dataProvider'=>$foodProvider

        'columns'=>array(

                'food_id',

                 'foodName', 

                 'fdqty', 

                 'fdprice', 

                   'fddiscount',

                array(

                        'class'=>'CButtonColumn',

                ),

        ),

)); ?>




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

        'id'=>'drink-grid',

        'dataProvider'=>$drinkProvider

        'columns'=>array(

                'drink_id',

                 'drName', 

                 'drqty', 

                 'drprice', 

                 'drdiscount',

                array(

                        'class'=>'CButtonColumn',

                ),

        ),

)); ?>




I hope it would work fine.

great!! that’s works bro,

but still, may the luck come to me,

the result cannot displayed in the gridView,

i use PostGreSQL in this case, since PostGreSQL got an error while i use the method you gave, i modified the controller, so it become like this :




public function actionView($id)

	{

		$this->loadModel($id);

		$foodProvider = new CActiveDataProvider ("Food", array (

		'criteria' => array( 

//i modified this part:		

'condition'=>Yii::app()->db->quoteColumnName('t.invoice_id').'=:id',

		

		'params' => array(":id" => $model->invoice_id),

		),)); 

		

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

		'model'=>//$model,==>

// i didn't use the method because it get the error 

//"Please specify the "data" property. "

		$this->loadModel($id),

		'foodProvider'=> $foodProvider, 

		));

	}

// my view are the same as you bro.,

// and i also try to use CListView but it comes with the same result (empty displayed //result)



my question bro,

  1. did the error (why can’t i display the data in my database) in my app was caused by the Postgre too?

  2. is there anything i should do in my relation(), or my foodController.php or the other related file to fix this error?

many thanks for your response bro,

my best regards. :)

bro seenivasan, thanks a lot for your help, finally i found the solution, i just need to rename my database, because, i found problem of postgresql which can’t differ the uppercase and lower case, i still use “invoiceId” in my database, n i replace all uppercase with “_” and it all works,

sry bro, it’s all my fault,thx a lot :lol:

it’s fun having discussion with you, hope we can have another,hehe

keep posting bro.