Complex Query To Model For Cgridview

I have a query is pretty simple, but does averaging and formatting of the results:

select s.ste_pca, p.pca_name, g.ras_group_name,

FORMAT(AVG(r.ras_total),2) as rastotal,

FORMAT(AVG(r.ras_pcmh1),2) as pcmh1,

FORMAT(AVG(r.ras_pcmh2),2) as pcmh2,

FORMAT(AVG(r.ras_pcmh3),2) as pcmh3,

FORMAT(AVG(r.ras_pcmh4),2) as pcmh4,

FORMAT(AVG(r.ras_pcmh5),2) as pcmh5,

FORMAT(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

where r.ras_group_id=2

group by ste_pca ;

Now I’m trying to figure out the best way to run this query so that it returns something that can be displayed in a cGridView. This is for reporting, so I’ll need the ability to use the sorting and filtering built into cGridview. I’ve searched for an example of something similar, but haven’t found it.

Thanks in advance.

Mark.

Hi Mark

I didn’t test that but you could check to combine the CSqlDataProvider and CGridView

http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

http://www.yiiframework.com/doc/api/1.1/CGridView

I am waiting you to tell us that it works! :)

Haven’t got this working yet.

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.