Extra on-the-fly properties for model objects from query

Yii 1.1 General Discussion
1

Hi,

Suppose I do some complex query using a CDbCriteria and CActiveRecord::findAll() to retrieve a list of instances of MyModel. Now suppose this query returns some extra data for each row which is not part of the normal properties of the model.

For example:


$criteria=new CDbCriteria(

  'select'=> '..., ..., [b]COUNT(*) AS occurrences[/b]',

  'with'=>array(

    //.... very complex 'with' including relations and child relations

  ),

  'condition'=>... //complex condition involving related objects

  'group'=>'t.id'

  'order'=>'occurrences DESC'

  'together'=>true

)

$items=MyModel::model()->findAll($criteria);

So far so good but then I would like to access the "occurrences" count like this:


echo $items[0]->occurrences

or anything similar.

I can’t because occurrences is not a property of MyModel and the occurrences data obtained in the query is discarded when populating the models.

So is there an elegant and "standard" way to store extra data obtained via a query into the model instances?

A hack that works fine is to define for example in this case a $occurrences property in the model:

class MyModel extends CActiveRecord {

public $occurrences;

}

Usually for any given instance of MyModel this property won’t be set; but when I do a query like the one above, it get populated.

This works but seems ugly. For one thing, in some occasion I may want to do this with some very specific and use-it-only-in-one-place property and I wouldn’t like to pollute the model class with a lot of ghost properties like this.

Is there a better way?

thanks

m.

2

Dear Matteosistesette,

I had similar experience.In user registration, I declared password1 as ghost property.

Is it that ugly?.

In your case,if you are trying to get the number of instances meeting specific conditions

did you find any difficulty in using the following queries?




$items=MyModel::model()->count($condition,$params);

$n=MyModel::model()->countBySql($sql,$params);
3

I resolved by extending the CActiveRecord class (and having all my model classes extend from the subclass) and overriding populateRecord() like this:


<?php

class ActiveRecord extends CActiveRecord

{

	

	protected $_queryResultData=array();

	

	

	public function populateRecord($attributes,$callAfterFind=true) {

		if ($attributes===false) return null;

		$record=parent::populateRecord($attributes,false);

		$record->_queryResultData=$attributes;

		if ($callAfterFind) $record->afterFind();

		return $record;

	}

	

	

	public function getQueryResultData($name=null) {

		if ($name==null) return $this->_queryResultData;

		else if (isset($this->_queryResultData[$name])) return $this->_queryResultData[$name];

		else return null;

	}

	

}

?>

Then, after any query that returns extra data in each row, I can access that data with $model->getQueryResultData(‘columnName’). In the example of my previous post it would be:

echo $items[0]->getQueryResultData(‘occurrences’);

@seenivasan count() only returns a single number; it counts the number of rows that satisfy a given condition. What I need is to retrieve a list of records, each one with a count, and I both need to use that count in the query for ordering purposes and then access it. Think for example of getting the list of the 10 most commented posts in a blog, or the posts with the greatest number of likes (where ‘like’ would be a many2many relation between Post and User), and then also display that number besides each post. No way you can do that with CActiveRecord::count().

4

Dear Friend,

Thanks for enlightening me on the limitations of count method of AR.

I have done once to get the top 5 posts with maximum comments, when i was learning blog tutorial.

I feel what you have done is better one,since it is dealt at database level.

Consider I have a Post model.

I have declared a relation to the Comment




public function relations()

	{

		

		return array(

                        //status 2 is approved.

			'comments' => array(self::HAS_MANY, 'Comment', 'post_id',

			'condition'=>'comments.status='2',

			'order'=>'comments.create_time DESC'),


			'commentCount'=>array(self::STAT,'Comment','post_id',

			'condition'=>'status='2'),


			'author' => array(self::BELONGS_TO, 'User', 'author_id'),

		);

	}

Now I can get the top 5 posts with maximum count.




$posts=Post::model()->findAll();

$arr=array();

foreach ($posts as $post) 

      {

        $arr[$post->title]=$post->commentCount;

      }


arsort($arr);

array_splice($arr,5);


foreach ($arr as $i=>$j)

      {

        echo $i."with".$j."comment(s)"."</br>";

      }
5

Wow, that would be absolutely unscalable. Imagine when your blog has one million posts, you’re loading all of them from the DB only to display five :)

I think it should be possible in your case to sort based on the STAT relation with a ‘order’ property in a CDbCriteria or something like that. However (see another recent post of mine about STAT relations) I get into some troubles when trying to use STAT relations.

Anyway here I was interested in general cases when you need pretty complicated queries that don’t fall into the case of simple count-like STAT relations.

6

That was done just to display titles of posts with maximum number of comments in portlet

not in main content area.