Ok. I got this working, sort of. By using the following code, I get a dataprovider that functions with cGridview using cSQLDataProvider.
$sql= 'SELECT c.cluster_name,
g.ras_group_name as group_name,
ROUND(AVG(r.ras_total),2) as rastotal,
ROUND(AVG(r.ras_pcmh1),2) as pcmh1,
ROUND(AVG(r.ras_pcmh2),2) as pcmh2,
ROUND(AVG(r.ras_pcmh3),2) as pcmh3,
ROUND(AVG(r.ras_pcmh4),2) as pcmh4,
ROUND(AVG(r.ras_pcmh5),2) as pcmh5,
ROUND(AVG(r.ras_pcmh6),2) as pcmh6
from ras_sum r
join ste s on r.ras_ptan=s.ste_ptan
join pca p on s.ste_pca=p.pca_code
join ras_group g on r.ras_group_id=g.ras_group_id
join cluster c on p.pca_cluster = c.cluster_id
where r.ras_group_id=1
group by cluster_name’ ;
$dataProvider=new CSqlDataProvider($sql, array(
‘keyField’=>‘cluster_name’,
‘pagination’=>false,
‘sort’=>array(
‘attributes’=>array(‘cluster_name’,‘group_name’,‘rastotal’,‘pcmh1’,‘pcmh2’,‘pcmh3’,‘pcmh4’,‘pcmh5’,‘pcmh6’,))
));
return $dataProvider;
It populates the cGridView and is sortable Had to change "FORMAT" to "ROUND" to get the correct order, but that a database thing, not a Yii issue. However, I have not figured out how to filter the information using cSQLDataProvider.
Has anyone done this? I found a post where someone rewrote it to use DBCriteria and Relations. I tried that, but could not find an example of how to model complex relationships and including them using the "$criteria->with=" structure.
here’s what I run into:
If tablename is set to "cluster" I can easily model the relationship between the "cluster" table and the "PCA" table. The PCA table contains a foreign key name "PCA_CLUSTER".
public function relations()
{
return array(
'clusterPca' => array(self::HAS_MANY, 'PCA', 'PCA_CLUSTER'),
);
}
Next, I have to define a relationship between the PCA and the STE tables. The STE table has a foreign key named “STE_PCA” This should be easy, and it probably is, but I sure can’t find where it’s documented or an example is given. The best I’ve been able to do with Relations is create a link between three tables by using a “BELONGS_TO” and a “HAS_MANY”. For instance, I can set PCA as the primary table. PCA belongs to a cluster, and has many sites. This can be modeled like this:
public function relations()
{
return array(
'pcaCluster' => array(self::BELONGS_TO, 'cluster', 'ras_cluster_id'),
'stePca' => array(self::HAS_MANY, 'STE', 'STE_PCA'),
);
}
This works, but unfortunately, it only represents three levels in schema. STEs have RAS_SUM, and RAS_SUMS belong to "RAS_GROUPS" Five tables, related in the following manner:
CLUSTER -< PCA -< STE -< RAS_SUM >- RAS_GROUP. I need information from CLUSTERS and RAS_GROUP, along with Averages from RAS_SUM grouped on CLUSTER.
Any Ideas? Better yet, Any examples?
Thanks in Advance.