Hello every one, I have a problem in counting and searching in a pivot table.
The scenario: I have a Reports table and its respective CActiveRecord Reports
+----+-------------+---------+--------+
| ID | reporter_id | subject | report |
+----+-------------+---------+--------+
| 1 | 1 | A | "OK" |
| 2 | 1 | B | "OK" |
| 3 | 1 | c |"NOT OK"|
| 4 | 2 | A | "OK" |
| 5 | 2 | C | "OK" |
+----+-------------+---------+--------+
Now, since Yii does not support view directly and I don’t want to add the view to the DB, I extended this model with ReporterReports. What this class do is to access the Reports table and group every report by reporter_id, like that:
+-------------+-----------------+-----------------+-----------------+
| reporter_id | report_subjectA | report_subjectB | report_subjectC |
+-------------+-----------------+-----------------+-----------------+
| 1 | "OK" | "OK" | "NOT OK" |
| 2 | "OK" | NULL | "OK" |
+-------------+-----------------+-----------------+-----------------+
The new model:
class ReporterReports extends Reports {
///the virtual attributes
public $reporterId;
public $reportSubjectA;
public $reportSubjectB;
public $reportSubjectC;
public static function model($class = __CLASS__){
return parent::model($class);
}
public function rules() {
return array(
array('reporterId, reportSubjectA, reportSubjectB, reportSubjectC', 'safe'),
array('reporterId, reportSubjectA, reportSubjectB, reportSubjectC', 'safe', 'on' => 'search')
);
}
private function getSelectStatement() {
return array(
'reporter_id as reporterId',
'MIN(CASE WHEN subject = 'A' THEN report ELSE NULL END) AS reportSubjectA',
'MIN(CASE WHEN subject = 'B' THEN report ELSE NULL END) AS reportSubjectB',
'MIN(CASE WHEN subject = 'C' THEN report ELSE NULL END) AS reportSubjectC',
);
}
//override findAll to add the pivoting query
public function findAll($condition='', $params=array()) {
$this->dbCriteria->select = $this->getSelectStatement();
$this->dbCriteria->group = "reporterId";
if(!empty($condition))
$this->dbCriteria->mergeWith($condition);
return parent::model()->findAll($this->dbCriteria, $params);
}
public function count($condition='',$params=array()) {
$this->dbCriteria->select = $this->getSelectStatement();
$this->dbCriteria->group = "reporterId";
if(!empty($condition))
$this->dbCriteria->mergeWith($condition);
return parent::model()->count($this->dbCriteria, $params);
}
public function search() {
$criteria = new CDbCriteria;
$criteria->select = $this->getSelectStatement();
$criteria->group = "reporterId";
$criteria->compare("reporter_id", $this->getReporterId(), true);
$criteria->compare('CASE WHEN subject = 'A' THEN report ELSE NULL END', $this->getReportSubjectA(), true);
$criteria->compare('CASE WHEN subject = 'B' THEN report ELSE NULL END', $this->getReportSubjectB(), true);
$criteria->compare('CASE WHEN subject = 'C' THEN report ELSE NULL END', $this->getReportSubjectC(), true);
return new CActiveDataProvider($this, array(
'criteria' => $criteria));
}
//getters and setters for virtual attributes
}
The Reports model: had to override the instantiate function so that it instantiate the correct class. If not, the new virtual attributes will not be selected.
class Reports extends CActiveRecord {
...
protected function instantiate($attributes)
{
if(isset($attributes['reporterId'])) {
$class='ReporterReports';
} else {
$class=get_class($this);
}
$model=new $class(null);
return $model;
}
}
My problems:
-
Count does not count the group. It counts all the Report records that will be grouped to create the ReporterReports record. So if we do a query and we get a ReporterReports record with subjectA and subcjectB, the count will be 2.
-
Cannot search in Gridview: with the current implementation when I search for a report, I get the correct record, but all the other reports are null. (probably because I cannot use the MIN function in the where clause)
-
I tried to directly access the attributes: since I’m selecting the attributes and give them a name with AS, I’m expecting to be able to access them, but I got column not found.
public function search() {
$criteria = new CDbCriteria;
$criteria->select = $this->getSelectStatement();
$criteria->group = "reporterId";
$criteria->compare("reporter_id", $this->getReporterId(), true);
$criteria->compare("reportSubjectA", $this->getReportSubjectA(), true);
....
}
I tried I think anything and the links given by google are all purple. Finally, I decided to ask help here.
Thanks