hi,
I created the following model
It actually uses other column names etc, this is pure for illustration.
class TestData extends CModel
{
/* the container to keep the attributes */
private $__attributes = array();
/**
* construct the model
*
* @return TestData
*/
public function __construct( )
{
//do whatever you need to do.
}
/**
* The available attributes
*
* @return Array the attributes
*/
public function attributeNames()
{
return array(
'Time',
'ColumnA',
'ColumnB',
);
}
/**
* The attribute labels
*
*/
public function attributeLabels()
{
return array(
'Time' =>'Time',
'ColumnA' =>'Column in table A',
'ColumnB' =>'Column in table B',
);
}
/**
* The search function
* @return CSqlDataProvider
*/
public function search()
{
$criteria=new CDbCriteria;
//setup the search variables
$criteria->compare('__t.Time',$this->Time,true);
$criteria->compare('ColumnA',$this->ColumnA,true);
$criteria->compare('ColumnB',$this->ColumnB,true);
//setup sorting
$sort = new CSort();
$sort->defaultOrder = 'Time DESC';
$sort->attributes = $this->attributeNames();
//creatae the command
$command->select = '
__t.Time AS Time,
ColumnA,
ColumnB
';
$command->from = '(
SELECT __a.Time FROM TableA __a UNION
SELECT __b.Time FROM TableB __b
) as __t';
$command->join = '
LEFT JOIN TableA ON TableA.Time = __t.Time
LEFT JOIN TableB ON TableB.Time = __t.Time
';
$command->where = $criteria->condition;
$command->params = $criteria->params;
//count the total number of rows
$countSql = 'SELECT COUNT(*) FROM ('.$command->text.') AS __c';
$count = Yii::app()->db->createCommand($countSql)->queryScalar($command->params);
//create the dataprovider
return new CSqlDataProvider($command,array(
'keyField'=>'Time',
'totalItemCount'=>$count,
'sort'=>$sort,
'pagination'=> array(
'pageSize'=>5,
)
));
}
/**
* set attributes
*
* @param mixed $name
* @param mixed $value
*/
public function __set($name,$data)
{
//are the attributes being set?
if( $name == 'attributes' )
{
//only use when data is array
if(is_array($data))
{
//check all values in array
foreach( $data as $name => $value )
{
//is the requested name a valid attribute?
if( in_array( $name , $this->attributeNames() , true ) )
{
//set the attribute
$this->$name = $value;
}
}
//done
return;
}
}
else if( in_array( $name , $this->attributeNames() , true ) )
$this->__attributes[$name] = $data;
else
parent::__set($name ,$data);
}
/**
* get attributes
*
* @param mixed $name
*/
public function __get($name)
{
//get attributes
if( in_array( $name , $this->attributeNames() , true ) )
return isset( $this->__attributes[$name] ) ? $this->__attributes[$name] : null;
parent::__get($name);
}
}
With this model i can create a dataprovider with any column i like from every table with a time column.
And with this data i can fill a gridview which is sortable and filterable for all columns.
It works great!!
But i was wondering, is this a normal way of doing this?
Or is there a way simpler method?