I’m having difficulty access the return values of my MySQL query.
$criteria = new CDbCriteria;
$criteria->select = "SUM(page_view_count) AS pageViewCount, SUM(conversion_count) as conversionCount, COUNT(*) AS visitCount, id";
$criteria->condition = "t.visitor_key = '".$this->visitor_key."'";
$criteria->group = 't.id';
$criteria->with = 'visitor';
$result = Visit::model()->find($criteria);
$visitCount = $result->visitCount; // error message "Property "Visit.visitCount" is not defined"
//$visitCount = $result->visitCount; // error message ""Property "Visit.0" is not defined""
Am I accessing the values correctly, or should I be using another method/object to run this query?
I think you can use Statistical Query, put the aggregation query in the visitor model’s relations. Check this guide: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#statistical-query
Yes, I worked through that. My issue is that the ‘visitor_key’ is not a PK or FK and using the STAT relation has not really helped. I thought I would simply access the result columns of the SQL query but not certain how to do that.
Hmm… the easiest way is to add the properties ($pageViewCount, $conversionCount etc) to the model. But it’s quite weird solution I think
Another way that I thought is to add the statistical query (if you can relate those 2 models), and about the ‘visitor_key’ we will put it in the criteria:
in the Visitor model:
public function relations()
'visits' => ... // you may already have this relation
'pageViewCount' => array(self::STAT, 'Visit', 'visitor_id', 'select' => 'SUM(page_view_count)'),
in the controller:
$result = Visitor::model()
Maybe there is a better solution, let’s see what others respond.
Hi G!! and explorer,
I think defining the public variable named "visitCount" in the model class should solve the problem, just as explorer has suggested.
class Visit extends CActiveRecord
Then Yii will use it as the place to store the value when you select some value as ‘visitCount’,
I don’t think it’s very weird. It’s simple and clear.
And IMHO, STAT relation is not very convenient because you can not filter and/or sort by it.
If I may add, using STAT relations were the cleanest solution. Adding [font=“Courier New”]pageViewCount[/font] and [font=“Courier New”]conversionCount[/font] properties to the model feels a lot like abuse to me. If you absoluteley have to fetch all data in one query, I’d suggest going through DAO instead.
We opted to add a foreign key relationship and then use self::STAT for the count & sum values.
'visitCount'=>array( self::STAT, 'Visit', 'visitor_id' ),
'viewCount'=>array( self::STAT, 'Visit', 'visitor_id', 'select' => 'SUM(page_view_count)', ),
Worked like a charm, very clean!
Thank you for all the ideas.