Cake PHP show videos ORDER BY rating

Now I began to get acquainted with the Cake PHP.

The following is the problem: I have two tables. The two models look like this:

class Video extends AppModel {
var $ name = 'Video';
var $ hasMany = array ('Rating' =>
array ('className' => 'Rating',
'foreignKey' => 'model_id'
'Conditions' => array ('Rating.model' => 'Video');
'dependent' => true,
'exclusive' => true
)
);
}


class Rating extends Model {
var $ name = 'Rating';

var $ validate = array ('user_id'开发者_开发知识库 => array ('rule' => array ('MaxLength', 36),
'required' => true);
'model_id' => array ('rule' => array ('MaxLength', 36),
'required' => true);
'model' => array ('rule' => 'alphanumeric',
'required' => true));
}

It looks like a query that works is:

$ this-> paginate = array (
'Video' => array (
'limit' => 15,
'page' => 1,
'order' => array (
'Video.id' => 'DESC')
)
);
$ this-> set ('videos', $ this-> paginate ("Video"));

I would like to sort by rating

My tables:

CREATE TABLE `ratings` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `user_id` char(36) NOT NULL default '',
  `model_id` char(36) NOT NULL default '',
  `model` varchar(100) NOT NULL default '',
  `rating` tinyint(2) unsigned NOT NULL default '0',
  `name` varchar(100) default '',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `rating` (`model_id`,`model`,`rating`,`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

CREATE TABLE `videos` (
  `id` int(11) NOT NULL auto_increment,
  `link` varchar(200) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=61 ;

Thank you in advance for answers.

Sorry for my English

In my opinion you should have an average rating field in your Video table. This will improve performance and make it really easy to do what you're asking for above.

After a user adds a new rating. Use the following formula to re-calculate the field:

new_avg_rating = (old_avg_rating + rating) / rating_count;

Similarly if a user removes a rating:

new_avg_rating = (old_avg_rating - rating / rating_count;

"rating" is the rating being added or deleted, rating_count is the number of records in the "rating" table for that video. Since you do this after adding/deleting the new rating to the table it already includes the new one.