Yii Framework Forum: Complex Query To Model For Cgridview - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Complex Query To Model For Cgridview Rate Topic: -----

#1 User is offline   Mark Rouse 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 12-September 12

Posted 06 November 2012 - 05:28 PM

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.
0

#2 User is offline   KonApaz 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,325
  • Joined: 21-February 11
  • Location:Greece

Posted 06 November 2012 - 05:46 PM

Hi Mark


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

http://www.yiiframew...SqlDataProvider
http://www.yiiframew...i/1.1/CGridView

I am waiting you to tell us that it works! :)
Yii is the best php framework in the world!
find our demo Yii extension on www.webkit.gr
Is it post useful? please v++ ;)
0

#3 User is offline   Mark Rouse 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 12-September 12

Posted 09 November 2012 - 04:07 PM

Haven't got this working yet.
0

#4 User is offline   Mark Rouse 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 12-September 12

Posted 14 November 2012 - 05:43 PM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users