How to Get data from Multiple tables through CActiveDataProvider

sridharkarra · January 31, 2012, 9:58am

Hello,

  I am new to YII and am not getting a particular solution.

I have generated 2 Model classes(people and supplier) with gii code generator

people table will contain the records for customers, employees, suppliers etc

I have a form which is saving the data in 2 tables. In one table, its primary and the same is foreign key in other table. I am having 2 tables, phppos_people and phppos_suppliers. When I am adding a supplier, data is getting saved in phppos_people where a Primary Key is getting generated. Few values are going to phppos_suppliers table with the auto generated id of phppos_people as one column in phppos_suppliers and its a foreign key.

Relations function of my People model class is as below :

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(			


		'phpposSuppliers' =&gt; array(self::HAS_MANY, 'PhpposSuppliers', 'person_id','index'=&gt;'person_id','together'=&gt;true ),


	);


}

My suppliers listing function in the controller is as below :

public function actionIndex()

{


    &#036;dataProvider=new CActiveDataProvider('PhpposPeople');


	&#036;this-&gt;render('index',array(


		'dataProvider'=&gt;&#036;dataProvider,            


	));


    


}

Its showing all the records from people table no matter whether they are available in suppliers table or not. How can I use the relations in Model class to get the data which are available in both the tables.

maris · January 31, 2012, 10:26am

Try this, I think it will work. If not, write me back, I will give you working example.

$dataProvider = new CActiveDataProvider(‘PhpposPeople’, array(

‘with’ => ‘phpposSuppliers’,

));

then in view:

$data->phpposSuppliers->some_attribute;

sridharkarra · January 31, 2012, 11:11am

Its not working for me. I am getting the below error :

Trying to get property of non-object

Can you get me a working example of it.

My project basically have many roles like employees, customers, suppliers etc. Common fields like first name, last name, email, mobile etc are stored in people table and the specific fields related to each role are stored in the respective tables with a foreign key which is primary in people table.

Now to show a list of suppliers, I need to join the suppliers and people table and show only the matching records and likewise for employees, customers etc.

Currently, for suppliers list, all the records from the people table are showing. I need to show only the matching records.

Controller class code

public function actionCreate()

{


	&#036;model=new PhpposPeople;


	&#036;model1=new PhpposSuppliers;


	// Uncomment the following line if AJAX validation is needed


	// &#036;this-&gt;performAjaxValidation(&#036;model);





	if(isset(&#036;_POST['PhpposPeople']))


	{


        &#036;transaction=Yii::app()-&gt;db-&gt;beginTransaction();


        try{


            &#036;model-&gt;attributes=&#036;_POST['PhpposPeople'];                


            &#036;model1-&gt;attributes=&#036;_POST['PhpposSuppliers'];


            if(&#036;model-&gt;save())


            {


                &#036;model1-&gt;person_id=&#036;model-&gt;person_id;


                if(&#036;model1-&gt;save())


                {


                    &#036;transaction-&gt;commit();


                    &#036;this-&gt;redirect(array('view','id'=&gt;&#036;model-&gt;person_id));


                }                    


            }


            &#036;transaction-&gt;rollBack();


        }


        catch(Exception &#036;e) { // an exception is raised if a query fails


            //something was really wrong - exception&#33;


            &#036;transaction-&gt;rollBack();





            //you should do sth with this exception (at least log it or show on page)


            Yii::log( 'Exception when saving data: ' . &#036;e-&gt;getMessage(), CLogger::LEVEL_ERROR );


        }


	}			


	&#036;this-&gt;render('create',array(


		'model'=&gt;&#036;model,


		'model1'=&gt;&#036;model1,


	));		


}

Create View Code

<?php

$this->menu=array(

array('label'=&gt;'List Suppliers', 'url'=&gt;array('index')),


array('label'=&gt;'Manage Suppliers', 'url'=&gt;array('admin')),

);

?>

<h1>Create Supplier</h1>

<?php echo $this->renderPartial(’_form’, array(‘model’=>$model,‘model1’=>$model1)); ?>

My _form view code is this

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

'id'=&gt;'phppos-people-form',


'enableAjaxValidation'=&gt;false,

)); ?>

&lt;p class=&quot;note&quot;&gt;Fields with &lt;span class=&quot;required&quot;&gt;*&lt;/span&gt; are required.&lt;/p&gt;





&lt;?php echo &#036;form-&gt;errorSummary(&#036;model); ?&gt;


&lt;?php echo &#036;form-&gt;errorSummary(&#036;model1); ?&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'first_name'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'first_name',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'first_name'); ?&gt;


&lt;/div&gt;    





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'last_name'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'last_name',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'last_name'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'phone_number'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'phone_number',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'phone_number'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'email'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'email',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'email'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'address_1'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'address_1',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'address_1'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'address_2'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'address_2',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'address_2'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'city'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'city',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'city'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'state'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'state',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'state'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'zip'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'zip',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'zip'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'country'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'country',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'country'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model,'comments'); ?&gt;


	&lt;?php echo &#036;form-&gt;textArea(&#036;model,'comments',array('rows'=&gt;6, 'cols'=&gt;50)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model,'comments'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model1,'account_number'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model1,'account_number',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model1,'account_number'); ?&gt;


&lt;/div&gt;





&lt;div class=&quot;row&quot;&gt;


	&lt;?php echo &#036;form-&gt;labelEx(&#036;model1,'company_name'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model1,'company_name',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


	&lt;?php echo &#036;form-&gt;error(&#036;model1,'company_name'); ?&gt;


&lt;/div&gt;








&lt;div class=&quot;row buttons&quot;&gt;


	&lt;?php echo CHtml::submitButton(&#036;model-&gt;isNewRecord ? 'Create' : 'Save'); ?&gt;


&lt;/div&gt;

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

</div>

Insertion is working fine in both the tables. Listing is not working properly that is all the records from people table are coming.

And in the Update, values in the controls of suppliers model class are not getting populated.

Please help me out

sridharkarra · February 1, 2012, 11:33am

I got it now. we need to access it like this :

$modelname->relationname->attribute

Thanks Maris. You gave me this solution but I did not understand at that point of time