If sorting is enough there are some working solutions. I prefer this one: http://www.yiiframework.com/forum/index.php?/topic/9400-sorting-dataprovider-on-statistical-query-relation/page__p__79691#entry79691 (altough I think that points and user_points are mixed up in the text).
The main "trick" is to add a custom CSort instance to your dataProvider, so that yii knows how to sort your column.
Translated to your example this would be:
public function relations() {
return array(
'jobApplications' => array(self::HAS_MANY, 'JobApplication', 'jobId'),
'numApplications'=>array(self::STAT, 'JobApplication', 'jobId'),
);
}
[...]
public function search(){
$criteria = new CDbCriteria;
#the following lines need to be executed before the first compare() to numApplications
$criteria->with = array('numApplications');
$criteria->together = true;
$criteria->group = "t.id";
$criteria->compare('id', $this -> id, true);
[...]
$criteria->compare('numApplications', $this->numApplications, true);
$sort = array('attributes'=>array(
'numApplications'=>array(
'asc'=>'numApplications',
'desc'=>'numApplications DESC',
'default'=>'desc',
),
'*',
));
return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria,
'sort' => $sort
}
Filtering is a bit trickier. AFAIK you cannot use aggregate functions directly in a WHERE statement, so the following won’t work:
SELECT t.* FROM Job AS t, COUNT(j.id) AS numApplications
LEFT JOIN JobApplications AS j ON (j.job_id = t.id) WHERE (numApplications > 5)
but this will (if I didn’t make a mistake):
SELECT t.* FROM Job AS t WHERE (SELECT COUNT(j.id) FROM JobApplications AS j WHERE (j.job_id = t.id) > 5
So now you have to force yii into creating a statement of the lower type. This is needed in two places: count() (used by CGridView to determine the number of results - for pagination and similar things) and findAll() (used to retrieve the results). So you need to add three functions to your Model class:
/**
* Override CActiveRecord count to allow filtering for a result of
* a SQL aggregate function.
*/
public function count($condition='',$params=array()){
$this->fixCondition($condition);
return parent::count($condition,$params);
}
/**
* Override CActiveRecord findAll to allow filtering for a result of
* a SQL aggregate function.
*/
public function findAll($condition='',$params=array()){
$this->fixCondition($condition);
return parent::findAll($condition, $params);
}
private function fixCondition($condition){
$column = 'bewertungAvg';
$replacement = '(SELECT ROUND(AVG(b.bewertung),0) FROM bewertung AS b WHERE b.idee_fk = t.id)';
$sqlSeparator = ' AND ';
if(!is_object($condition) ||
strpos($condition->condition, $column) === false){
return;
}
$parts = explode($sqlSeparator, $condition->condition);
$compare = '';
foreach($parts as $id => $part){
$findpos = strpos($part, $column);
if($findpos !== false){ #remove condition that uses the non existent column
$startpos = $findpos + strlen($column);
$endpos = strpos($part, ')'); #check for closing bracket
if($endpos === false){
$compare = substr($part, $startpos);
} else { #remove closing bracket
$compare = substr($part, $startpos, $endpos-$startpos);
}
unset($parts[$id]);
break;
}
}
$parts[] = $replacement.$compare; #replace the removed condition
$condition->condition = join($parts, $sqlSeparator);
}
I might have made some mistakes trying to apply my code to your model, but other than that this should work.
HTH
jak
<edit>small bugfix in fixCondition (filter did not work when statement was not in brackets)</edit>
<edit>Clarification in search()</edit>
