Ordering by number of hasMany records - COUNT(RelatedModel.field)

Question

I have a model, Track, that hasMany Vote. the Vote model has a field called "value". What I wish to do is to sort Track records by the sum of the values of their votes (which means unfortunately I can't use counterCache).

Of course, trying to put something like 'order'=>'SUM(Vote.value)' fails with an "unknown column..." error.

I would just get everything in a find('all') and then sort it out afterwards, but I need pagination too.

The best idea I've come up with is to add a field to the Track model that keeps track of the total value of all votes - something like what counterCache does but reflecting the different value of various votes.

Is there an easier way to do this?

Thanks for reading!

Answer 1

Well, I ended up adding a column ("vote_score") to the Track model and updating it when a vote is added/deleted. Here's the code, in case anyone is interested. It appears to work, haven't tested it extensively though. In the Vote model:

public function afterSave(){
    $this->Track->id = $this->data['Vote']['track_id'];
    $this->Track->saveField('vote_score','vote_score' + $this->data['Vote']['value']);
}

public function beforeDelete(){
    $vote = $this->Vote->read();
    $this->Track->id = $vote['Vote']['track_id'];
    $this->Track->saveField('vote_score','vote_score' - $vote['Vote']['value']);
    return true;
}