Joining three tables and arrange

Dear Everybody,

I have three tables with similar text field, no matter with datas.

I want to join all three tables and process the result datewise only.

As I am a newbie I am unable to do this in yii. Please anybody help me.

You can do this by following ways:

  1. Updating below code in model’s search method.

  2. Create new method and add the below code in that particular "new method" in the model.


$records = array();


$prov1 = new CActiveDataProvider('Model1', array(

    'criteria' => array()

));

 

$prov2 = new CActiveDataProvider('Model2', array(

          'criteria' => array()

));


$prov3 = new CActiveDataProvider('Model3', array(

          'criteria' => array()

));


// merge all data

$records=array_merge($prov1->data , $prov2->data, $prov3->data);

 

$provAll = new CArrayDataProvider($records,

    array(

        'sort'=>array("defaultOrder"=>"date DESC"),

        'pagination' => array('pageSize' => 10) //optional add a pagination

    )

);


$provAll->keyField = 'id'; 


return $provAll;



Further, you require to set dataProvider of CGridView according to your model’s method (newly created or search method) inside your view file.

Let me know if you face any query/concern regarding this.

Thanks!

I am not really getting it. How to display in view ?

I just want to join 3 table and show it in view datewise only.

Let me provide you the detailed solution:

In your model update the search method as described below:


public function search()

{

  // Warning: Please modify the following code to remove attributes that

  // should not be searched.


  $criteria=new CDbCriteria;


  $criteria->compare('primary_key',$this->primary_key);

  $criteria->compare('first_field',$this->first_field,true);

  $criteria->compare('second_field',$this->second_field,true);

  $criteria->compare('date_field',$this->date_field,true);


  $records = array();


  $prov1 = new CActiveDataProvider('Model1', array(

      'criteria' => $criteria,

  ));

   

  $prov2 = new CActiveDataProvider('Model2', array(

      'criteria' => $criteria,

  ));


  $prov3 = new CActiveDataProvider('Model3', array(

      'criteria' => $criteria,

  ));


  //or you could use 

  $records=array_merge($prov1->data , $prov2->data, $prov3->data);

   

  $provAll = new CArrayDataProvider($records,

      array(

          'sort'=>array("defaultOrder"=>"date_field DESC"),

          'pagination' => array('pageSize' => 10) //optional add a pagination

      )

  );


  $provAll->keyField = 'primary_key'; 


  return $provAll;

}

In your controller’s action do the needful changes as described below:


public function actionAdmin()

{

  $model=new Model1('search');

  $model->unsetAttributes();  // clear any default values

  if(isset($_GET['Model1']))

    $model->attributes=$_GET['Model1'];

  

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

    'model'=>$model,

  ));

}

Lastly search method should be called in view file, as described below.


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

  'id'=>'test1-grid',

  'dataProvider'=>$model->search(),

  'filter'=>$model,

  'columns'=>array(

    'first_field',

    'second_field',

    'date_field',

    array(

      'class'=>'CButtonColumn',

    ),

  ),

)); ?>

You need to update the name of your model and fields accordingly.

I think there is problem for me to make question clear.

Actually, I have 3 tables: tableA, tableB and tableC. They do not have any type of relations.

They are are different but the text field are same tableA(id, name, address, date), tableB(id, name, address, date), tableC(id, name, address, date).

Now I need to display all the data at one view, lets say index page.

I need all datas from 3 tables assorted and arranged datewise in one view file.

tableA data 23-5-2015

tableC data 22-5-2015

tableB data 21-5-2015

tableB data 19-5-2015

tableA data 15-5-2015

tableB data 12-5-2015

tableC data 10-5-2015