This is my attempt to bring the statistical relational attributes from MANY-MANY relational and HAS-MANY through relational rules and do sorting based on these relational attributes.
I tried to create MANY-MANY reletionship between patient and diseases.
One patient has many diseases and a disease is found in many patients.
I have 3 tables.
1.patient. (id,name)
2.patient_morbidity. (id,p_id,d_id) ####THE JOIN TABLE####
3.morbidity. (id,disease)
p_id is the foreign key referencing patient.id
d_id is the foreign key referencing morbidity.id
The following is the content of relations method in AR class Morbidity.php.
public function relations()
{
return array(
'patient_morbidities' => array(self::HAS_MANY, 'Patient_morbidity', 'd_id'),
'patients'=>array(self::MANY_MANY,'Patient','patient_morbidity(p_id,d_id)'),
'patientCount'=>array(self::STAT,'Patient','patient_morbidity(p_id,d_id)'),
//'patients'=>array(self::HAS_MANY,'Patient',array('p_id'=>'id'),'through'=>'patient_morbidities'),
//'patientCount'=>array(self::STAT,'Patient',array('p_id'=>'id'),'through'=>'patient_morbidities'),
);
}
I am trying to get both the patient names and patient numbers for a particular disease.
I have used here MANY-MANY relation rule here to acheive this.
You might have noticed I have commented out a piece of code. Here I tried
HAS-MANY through relation rule. But it failed to bring the number of patients.
It has thrown the following error:
The advantage of using HAS-MANY through relation rule (if I am correct)
is we can progressively ascend up or descend down the relationship ladder and
can acheive remote relationship by going across many tables.
Yii document has not mentioned and has not given any examples for
STAT relation in the context of HAS-MANY through relation rule.
Though there is work around for this, I do not know whether it is a good practice.
In our example, if we have used the following rule in Morbidity.php based on HAS-MANY through rule,
public function relations()
{
return array(
'patient_morbidities' => array(self::HAS_MANY, 'Patient_morbidity', 'd_id'),
'patients'=>array(self::HAS_MANY,'Patient',array('p_id'=>'id'),'through'=>'patient_morbidities'),
);
}
We can declare a property in the Morbidity.php.
public $patientCount;
If we have an instance of Morbidity, we can assign a value to the property patientCount.
$morbidity=Morbidity::model()->find();
$morbidity->patientCount=count($morbidity->patients);
Now the fun part begins!
There may be a need for sorting records based on the relational attribute.
To utilise the full advantage of CListView,Here we can bring the wonders of CArrayDataProvider.
Now we will try to sort the diseases based on the number of patients affected.
Rewrite the actionIndex method in MorbidityController.php.
/**
* Lists all models.
*/
public function actionIndex()
{
$dataProvider=new CArrayDataProvider(Morbidity::model()->findAll(),
array(
'sort'=>array('attributes'=>array('patientCount')),
'pagination'=>array('pageSize'=>10),
));
$this->render('index',array(
'dataProvider'=>$dataProvider,
));
}
In corresponding views folder, declare sortableAttributes in CListView.
<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view',
'sortableAttributes'=>array('patientCount')
)); ?>
Do not forget to add relational attribute in _view.php.
<b><?php echo CHtml::encode($data->getAttributeLabel('No of patients affected')); ?>:</b>
<?php echo CHtml::encode($data->patientCount); ?>
<br />
Now if we have used HAS-MANY through relation rule,
You have to get the value for patientCount property.
For this purpose we are going to declare a static function
inside the Morbidity.php.
public static function addPatientCount() {
$arr=array();
foreach(Morbidity::model()->findAll() as $object) {
$object->patientCount=count($object->diseases);
$arr[]=$object;
}
return $arr;
}
Now rewrite the actionIndex method in MorbidityController.php accordingly.
/**
* Lists all models.
*/
public function actionIndex()
{
$dataProvider=new CArrayDataProvider(Morbidity::addPatientCount(),
array(
'sort'=>array('attributes'=>array('patientCount')),
'pagination'=>array('pageSize'=>10),
));
$this->render('index',array(
'dataProvider'=>$dataProvider,
));
}
Now we can have the liberty of sorting of records based on relational statistical attribute.
Really doubts are raised over using count method to bring the relational attribute.
Is it really safe? Is it going to bring the empty objects in larger database?. I do not know.
############## One more simple approach is to create a view in database. #################################
In our example, we can create a view named "dview".
CREATE
ALGORITHM = UNDEFINED
VIEW `dview`
(id,disease,patientCount)
AS SELECT morbidity.id,disease,COUNT(d_id) FROM `morbidity` INNER JOIN patient_morbidity
WHERE morbidity.id=patient_morbidity.d_id
GROUP BY disease
Now generate the model Dview by Gii. No need for controller or views.
In MorbidityController.php, modify the actionIndex method.
/**
* Lists all models.
*/
public function actionIndex()
{
$dataProvider=new CActiveDataProvider('Dview',
array(
'sort'=>array('attributes'=>array('patientCount')),
'pagination'=>array('pageSize'=>10),
));
$this->render('index',array(
'dataProvider'=>$dataProvider,
));
}
Add sortableAttributes property in CListView in index.php.
<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view',
'sortableAttributes'=>array('patientCount'),
)); ?>
Do not forget to add relational attribute in _view.php.
<b><?php echo CHtml::encode($data->getAttributeLabel('No of patients affected: ')); ?>:</b>
<?php echo CHtml::encode($data->patientCount); ?>
<br />