Complex SQL?

Can someone point me in the right direction. How would I go about constructing the following statement.

The problem is the Yii generated models use this format:

And I am not that familiar with it yet… NOOBY!


public function search()

	{

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

		// should not be searched.


		$criteria=new CDbCriteria;


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


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


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


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


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


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


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


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


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


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


		return new CActiveDataProvider('Pms', array(

			'criteria'=>$criteria,

		));

	}


SELECT asset_no, 

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date`, '%b' ) = 'Jan' , 1, 0 ) ) AS Jan,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Feb' , 1, 0 ) ) AS Feb,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Mar' , 1, 0 ) ) AS Mar,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Apr' , 1, 0 ) ) AS Apr,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'May' , 1, 0 ) ) AS May,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Jun' , 1, 0 ) ) AS Jun,

sum( if( `completion_date` <= `schduled_date` AND date_format(`completion_date` , '%b' ) = 'Jul' , 1, 0 ) ) AS Jul,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Aug' , 1, 0 ) ) AS Aug,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Sep' , 1, 0 ) ) AS Sep,

sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Oct' , 1, 0 ) ) AS Oct,

sum( if( `completion_date` <=`schduled_date` AND date_format(`completion_date` , '%b' ) = 'Nov' , 1, 0 ) ) AS Nov,

sum( if( `completion_date` <= `schduled_date` AND date_format(`completion_date` , '%b' ) = 'Dec' , 1, 0 ) ) AS `Dec`  

FROM

 assets, pms 

WHERE 

`completion_date` <= `schduled_date` AND `completion_date` <> '0000-00-00' AND asset_parent_id = asset_id 

GROUP BY 

asset_no ORDER BY `completion_date



Here’s the API reference for CDbCriteria

Let’s start to build the criteria




public function search()

{

  $criteria=new CDbCriteria;


  $criteria->select = array(..., ..., ...);

  $criteria->condition = 'completion_date <= schduled_date AND completion_date <> "0000-00-00" AND asset_parent_id = asset_id';

  $criteria->group = 'asset_no';

  $criteria->order = 'completion_date';

  $criteria->with = array('assets');

  $criteria->together = true;


  ...

  return new CActiveDataProvider('Pms', array('criteria'=>$criteria,));

}



Notes:

  • Create a relationship called assets int the Pms model

  • SQL functions with more than one parameter must be embedded in a PHP array e.g. array(‘MAX(param1, param2)’)

/Tommy

This is the model:


<?php


class charts extends CActiveRecord{ 

public function relations()

	{

return array( 

                        'asset' => array(self::BELONGS_TO, 'Assets', 'asset_id'),                       

                        );


	}

	

  public $Jan;

  public $Feb;

  public $Mar;

  public $Apr;

  public $May;

  public $Jun;

  public $Jul;

  public $Aug;

  public $Sep;

  public $Oct;

  public $Nov;

  public $Dec;

     public function search()

{

  $criteria=new CDbCriteria;

         $criteria->select = array("sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date`, '%b' ) = 'Jan' , 1, 0 ) ) AS Jan, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Feb' , 1, 0 ) ) AS Feb, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Mar' , 1, 0 ) ) AS Mar, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Apr' , 1, 0 ) ) AS Apr, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'May' , 1, 0 ) ) AS May, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Jun' , 1, 0 ) ) AS Jun, 

		sum( if( `completion_date` <= `schduled_date` AND date_format(`completion_date` , '%b' ) = 'Jul' , 1, 0 ) ) AS Jul, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Aug' , 1, 0 ) ) AS Aug, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Sep' , 1, 0 ) ) AS Sep, 

		sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Oct' , 1, 0 ) ) AS Oct, 

		sum( if( `completion_date` <=`schduled_date` AND date_format(`completion_date` , '%b' ) = 'Nov' , 1, 0 ) ) AS Nov, 

		sum( if( `completion_date` <= `schduled_date` AND date_format(`completion_date` , '%b' ) = 'Dec' , 1, 0 ) ) AS `Dec`"); 

          $criteria->condition = 'completion_date <= schduled_date AND completion_date <> "0000-00-00" AND asset_parent_id = asset_id';

 		  $criteria->group = 'asset_no';

  		  $criteria->order = 'completion_date';

  		  $criteria->with = array('assets');

	      $criteria->together = true;


	      $result = charts::model()->find($criteria); 

	     

         

     } 

 } 



How do I get this output to the view?

Because of the alias??

Tried this in the view.php but didn’t work:




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


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

	'data'=>$model,

	'attributes'=>array(

		'Jan',

                'Feb',

                'Mar',

                'Apr',

                'May',

                'Jun',

                'Jul',

                'Aug',

                'May',

                'Jun',

                'Jul',

                'Aug'

                ,'Sep'

                ,'Oct'

                ,'Nov'

                ,'Dec'

)); ?>



And this in the _view.php


<div class="view">


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

	<?php echo CHtml::link(CHtml::encode($data->Jan), array('view', 'Jan'=>$data->Jan)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->Feb), array('view', 'Feb'=>$data->Feb)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->Mar), array('view', 'Mar'=>$data->Mar)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->Apr), array('view', 'Apr'=>$data->Apr)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->May), array('view', 'May'=>$data->May)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->Jun), array('view', 'Jun'=>$data->Jun)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->Jul), array('view', 'Jul'=>$data->Jul)); ?>

	<br />


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

	<?php echo CHtml::link(CHtml::encode($data->Aug), array('view', 'Aug'=>$data->Aug)); ?>

	<br />

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

	<?php echo CHtml::link(CHtml::encode($data->Sep), array('view', 'Sep'=>$data->Sep)); ?>

	<br />

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

	<?php echo CHtml::link(CHtml::encode($data->Oct), array('view', 'Oct'=>$data->Oct)); ?>

	<br />

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

	<?php echo CHtml::link(CHtml::encode($data->Nov), array('view', 'Nov'=>$data->Nov)); ?>

	<br />

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

	<?php echo CHtml::link(CHtml::encode($data->Dec), array('view', 'Dec'=>$data->Jan)); ?>

	<br />

</div>

I am sure its because I am missing something.

Any help would be apprecaited.

I recommend you try this out with a smaller chunk of code e.g a simpler select clause


$criteria->select = 'SUM(SomeAttribute) as SomeAlias';

Then you may want to inspect the data set before moving on with the view


echo '<pre>';print_r($model->search()->data);echo '</pre>';

When asking for clues, also tell us more about the error you see.

/Tommy

Sorry where would I test that? echo ‘<pre>’;print_r($model->search()->data);echo ‘</pre>’;

Or you can assign the data provider instance to a variable and dump the variable instead




  $dp = new CActiveDataProvider('Pms', array('criteria'=>$criteria,));


  //echo '<pre>';print_r($dp->data);echo '</pre>';

  //die;


  return $dp;



Edit: added data

/Tommy

This way the dump will become more readable




echo '<pre>';CVarDumper::dump($dp->data);echo '</pre>';



Anyway, you can adjust the criteria until you have a good dataset returned without error/CDbException. One interesting result would be if one of the complicated statements you want to use inside the SUM() function actually will pass without error.

/Tommy

That is in the logs correct.

Actually another right parentheses should be added to your CDetailView view.

Example access (first array element)




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

  'data'=>$model->search()->data[0],

  'attributes'=>array(

    'Jan',

    //...

  )

));



/Tommy

Thanks Tommy for your help.

Here is what I have tried:

IN THE MODEL: pms.php


<?php


/**

 * This is the model class for table "pms".

 *

 * The followings are the available columns in table 'pms':

 * @property integer $id

 * @property integer $asset_id

 * @property string $type_of_maintenance

 * @property integer $scheduled_maintenance

 * @property integer $unscheduled_maintenance

 * @property string $schduled_date

 * @property string $completion_date

 * @property string $downtime

 * @property string $description_of_work

 * @property integer $company_id

 *

 * The followings are the available model relations:

 * @property Assets $asset

 * @property Companies $company

 */

class Pms extends CActiveRecord

{

public $alias_1;

	/**

	 * Returns the static model of the specified AR class.

	 * @return Pms the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'pms';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('asset_id, type_of_maintenance, scheduled_maintenance, unscheduled_maintenance, schduled_date, completion_date, downtime, description_of_work, company_id', 'required'),

			array('asset_id, scheduled_maintenance, unscheduled_maintenance, company_id', 'numerical', 'integerOnly'=>true),

			array('type_of_maintenance', 'length', 'max'=>255),

			array('downtime', 'length', 'max'=>77),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, asset_id, type_of_maintenance, scheduled_maintenance, unscheduled_maintenance, schduled_date, completion_date, downtime, description_of_work, company_id', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	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(

			'asset' => array(self::BELONGS_TO, 'Assets', 'asset_id'),

			'company' => array(self::BELONGS_TO, 'Companies', 'company_id'),

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'id' => 'ID',

			'asset_id' => 'Asset',

			'type_of_maintenance' => 'Type Of Maintenance',

			'scheduled_maintenance' => 'Scheduled Maintenance',

			'unscheduled_maintenance' => 'Unscheduled Maintenance',

			'schduled_date' => 'Schduled Date',

			'completion_date' => 'Completion Date',

			'downtime' => 'Downtime',

			'description_of_work' => 'Description Of Work',

			'company_id' => 'Company',

			'alias_1' => 'alias_1',

		);

	}


	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

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

		// should not be searched.


		$criteria=new CDbCriteria;

		$criteria->select =array('id,asset_id,type_of_maintenance as alias_1,scheduled_maintenance,unscheduled_maintenance,schduled_date,completion_date,downtime,description_of_work,company_id');

		/* $criteria->compare('id',$this->id);

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

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

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

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

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

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

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

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

		$criteria->compare('company_id',$this->company_id); */

		$criteria->group = 'id';

		$result = pms::model()->find($criteria); 

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			

		));

	}

}



Now this works for CGridView but no not CDetail or Anywhere else

Here is grid view or admin.php

NOTE: When I say works I have narrowed my code down to just working on getting the alias to work so I am using a known field (type_of_maintenance) and putting it to and alias called alias_1 and like I said it works in the gridview but nowhere else. Could you help?

When I try in view nothing.


div class="view">

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

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

	<br /></div>

And in _view.php nothing either:


<?php

$this->breadcrumbs=array(

	'Pms'=>array('index'),

	$model->id,

);


$this->menu=array(

	array('label'=>'List Pms', 'url'=>array('index')),

	array('label'=>'Create Pms', 'url'=>array('create')),

	array('label'=>'Update Pms', 'url'=>array('update', 'id'=>$model->id)),

	array('label'=>'Delete Pms', 'url'=>'#', 'linkOptions'=>array('submit'=>array('delete','id'=>$model->id),'confirm'=>'Are you sure you want to delete this item?')),

	array('label'=>'Manage Pms', 'url'=>array('admin')),

);

?>


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


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

	'data'=>$model,

	'attributes'=>array(

		'id',

		array('label'=>'Asset No.', 'value'=>$model->asset->asset_no),

		'type_of_maintenance',

		array('name'=>'scheduled_maintenance', 'value'=>$model->scheduled_maintenance?'Yes':'No'), 

        array('name'=>'unscheduled_maintenance', 'value'=>$model->unscheduled_maintenance?'Yes':'No'), 

		'schduled_date',

		'completion_date',

		'downtime',

		'description_of_work',

		'alias_1',

		

		array('label'=>'Company', 'value'=>$model->company->company_name),

	),

)); 

?>

Hint: how do you pass $data to view, $model to _view?

I guess you will have to study how the Gii generated CRUD really works, especially when search() is used or not, and when data is loaded from the controller e.g. loadModel().

There’s not very much documentation available covering the Zii widgets and their use in the CRUD code. And for customization you’ll have to search the forum for clues. You may also want to have a look at the new Yii Answers site.

I assume you already found the class reference and the guide. I’m not very familiar with the Blog Demo Tutorial (I think it has been updated since the original 1.0 edition) nor the other tutorials.

Hopefully you’ll soon have a good understanding of the different actions, views and their interaction with models. When asking for help, please tell us a little more about which of the use cases (list/view/update/admin) you are refering to.

/Tommy

Hopefully there will be someone who has worked with alias and have a solution… Thanks again bro.