Yii Framework Forum: How To Displaying Relational Tables For Report - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

How To Displaying Relational Tables For Report Yii Framework Forum: How To Displaying Relational Tables For Report Rate Topic: -----

#1 User is offline   rynd75 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-January 13

Posted 12 January 2013 - 09:41 PM

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 Attached File  display-relational-tables_6.png (19.81K)
Number of downloads: 32 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,
0

#2 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 13 January 2013 - 01:07 AM

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.
0

#3 User is offline   rynd75 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-January 13

Posted 13 January 2013 - 06:01 PM

View Postseenivasan, on 13 January 2013 - 01:07 AM, said:

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)

0

#4 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 14 January 2013 - 02:09 AM

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.
0

#5 User is offline   rynd75 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-January 13

Posted 14 January 2013 - 03:06 AM

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 :)
0

#6 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 14 January 2013 - 05:06 AM

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.
2. 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.
0

#7 User is offline   rynd75 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-January 13

Posted 14 January 2013 - 06:06 AM

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. :)
0

#8 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 14 January 2013 - 07:50 AM

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.
0

#9 User is offline   rynd75 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-January 13

Posted 14 January 2013 - 09:13 PM

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. :)
0

#10 User is offline   rynd75 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-January 13

Posted 18 January 2013 - 12:33 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users