Sorting by relation attibute

I have a couple of tables Post and Vote. Each post can be voted up or down by users. Below is the table schema.

Post => id, content, user_id

Vote => id, post_id, user_id, type (0->DOWN, 1->UP)

Relation in post model are defined as below.

	public function relations()

	{

		return array(

			...

			...

			'upVoteCount' => array(self::STAT, 'Vote', 'story_id',

    	                        'condition'=>'vote='.Vote::VOTE_UP),

			'downVoteCount' => array(self::STAT, 'Vote', 'story_id',

    	                        'condition'=>'vote='.Vote::VOTE_DOWN),

			...

			...				

		);

	} 

Now, I want to list the post by number of up or down vote counts, e.g. upVoteCount or downVoteCount. I have written the following code in Post controller for displaying posts by highest up vote count but apparantely it’s not working.

public function actionTop($duration = 365, $limit = 100)

	{

		$dataProvider=new CActiveDataProvider('Post', array(

												'criteria'=>array(

													'condition'=>'created>=:created',

													'params'=>array(

														':created'=>strtotime('-'.$duration.' days')

													),

													'with'=>array('upVoteCount'),

													'order'=>'upVoteCount DESC',

													'limit'=>$limit

												),

												'pagination'=>array(

													'pageSize'=>20

												)

											)

										);

		$this->render('index',array(

			'dataProvider'=>$dataProvider,

		));

	}

May be I need to join vote and post table somehow. Right now, I am getting unknown column upVoteCount in order clause. I am not good at writing SQL which is one of the reason I rely on frameworks to do majority of that work for me

Hi there,

I hope this wiki will be some help:

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview/

Thanks. I’ll give it a try.

Unfortunately this won’t work as I am not using grid view and sorting is not required as a column but I was to list posts by maximum number of votes or comments so a join would be needed probably.

I am wondering if there is an easy way (without direct SQL) of doing it,

The point of argument in the wiki I mentioned is not on CGridView, but on CActiveDataProvider.

If you are going to use CListView instead of CGridView, then virtually there’s no difference.

What do you want to use for the output list?

No, you didn’t get the point. There are no columns in my case with titles on which user will click to sort them by something. The search function helps in that kind of sorting as per my understanding.

In my case, I have a menu item call "top commented posts" and "top favorited posts" so I need to make a query to select posts ordered by most number of comments and favorites respectively.

Just to make sure we understand each other correctly, did you mean you are clear about what I am trying to do and still suggesting the same thing? Thanks!

Well, the point that I wanted to say was:

1. You can not make use of STAT relation for filtering and sorting.

2. Use sub query to retrieve the STAT value (COUNT, MAX, … etc)

Of course you have to modify the code in the wiki to fit your needs, but the key points in constructing the data provider are just the same.

Unfortunately there’s no simple way of doing it solely by active record - you have to use some raw sql approach.

Now, would you please try the following:

public function relations()

{

	return array(

		...

		...

// do not use 'upVoteCount' STAT relation

//		'upVoteCount' => array(self::STAT, 'Vote', 'story_id',

//			'condition'=>'vote='.Vote::VOTE_UP),

		'downVoteCount' => array(self::STAT, 'Vote', 'story_id',

			'condition'=>'vote='.Vote::VOTE_DOWN),

		...

		...                             

	);

} 


// declare 'upVoteCount' attribute instead

public $upVoteCount;


public function actionTop($duration = 365, $limit = 100)

{

	// sub query to retrive upVoteCount ... rough guess (you may want to correct it)

	$voteTable = Vote::model()->tableName;

	$sub_query = "(select count(*) from $voteTable where $voteTable.story_id = t.id and $vote_table.vote=" . VOTE::VOTE_UP . ")";


	$dataProvider=new CActiveDataProvider('Post', array(

		'criteria' => array(

			'select' => array(

				'*',

				$sub_query . " as upVoteCount",

			),

			'condition' => 'created>=:created',

			'params'=>array(

				':created' => strtotime('-'.$duration.' days')

			),

			// 'with' => array('upVoteCount'),  // do not use STAT relation

			'order' => 'upVoteCount DESC',

			'limit' => $limit,

		),

		// 'pagination' => array(

		//	'pageSize' => 20,

		// ),


	));

	

	$this->render('index',array(

		'dataProvider' => $dataProvider,

	));

}

As for a side note, CActiveDataProvider construct the “LIMIT” and “OFFSET” sql clauses from ‘pagination’ property, and “ORDER BY” from ‘sort’ property.

So if you want to use the provider with CGridView or CListView, then the code should be like this:

	$dataProvider=new CActiveDataProvider('Post', array(

		'criteria' => array(

			'select' => array(

				'*',

				$sub_query . " as upVoteCount",

			),

			'condition' => 'created>=:created',

			'params'=>array(

				':created' => strtotime('-'.$duration.' days')

			),

			// 'with' => array('upVoteCount'),  // do not use STAT relation

			// 'order' => 'upVoteCount DESC',

			// 'limit' => $limit,

		),

		'sort' => array(

			'defaultOrder' => 'upVoteCount DESC',

			'attributes' => array(

				'upVoteCount' => array(

					'asc' => 'upVoteCount ASC',

					'desc' => 'upVoteCount DESC',

				),

				'*',

			),

		),

		'pagination' => array(

			'pageSize' => $limit,

		),


	));

Wow! That worked perfectly. Thanks a lot!!!