i have standart cgridview with table that has join.
the problem is when cgridview is being drawn it generates this query
SELECT COUNT(DISTINCT
`t`.`sisfar_in_items_xml_id`) FROM `sisfar_in_items` `t` LEFT OUTER JOIN
`sisfar_customers` `customers` ON (sisfar_customers_id =
sisfar_in_items_created_by)
instead of this
SELECT COUNT(*) FROM `sisfar_in_items` `t` LEFT OUTER JOIN
`sisfar_customers` `customers` ON (sisfar_customers_id =
sisfar_in_items_created_by)
as result join takes about 6-15 seconds instead of instantly because with the query generated mysql doesn’t use index on sisfar_in_items_created_by field.
Any ideas how could i change that query?
model
<?php
class SisfarInItems2 extends CActiveRecord
{
public $created_by;
public $modified_by;
public $created_from;
public $created_till;
public $modified_from;
public $modified_till;
public $action;
public function scopes() {
return array(
'limitedUser' =>array(
'condition'=>'sisfar_users_sources_users_id='. Yii::app()->user->users_id,
//'with'=>'sources',
),
);
}
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'customers' => array(self::HAS_ONE, 'SisfarCustomers', '', 'on'=>'sisfar_customers_id = sisfar_in_items_created_by'),
'sources' => array(self::HAS_MANY, 'SisfarUsersSources', '', 'on' => 'sisfar_in_items_created_by = sisfar_users_sources_customers_id'),
);
}
public function receivedMailItems()
{
//file_put_contents(Yii::app()->basePath.'/data/tmp/debug.txt', date('h:m:s').' '.json_encode($_GET)."\n", FILE_APPEND);
// Warning: Please modify the following code to remove attributes that
// should not be searched.
//$tmp = $this->getScenario();
//file_put_contents(Yii::app()->basePath.'/data/tmp/debug.txt', date('h:m:s').' '.json_encode($this->getScenario()), FILE_APPEND);
$criteria=new CDbCriteria;
$criteria->compare('t.sisfar_in_items_xml_id',$this->sisfar_in_items_xml_id);
$criteria->compare('t.sisfar_in_items_ips_mailitm_pid',$this->sisfar_in_items_ips_mailitm_pid,true);
$criteria->compare('t.sisfar_in_items_ItemId',$this->sisfar_in_items_ItemId,true);
$criteria->compare('t.sisfar_in_items_xml',$this->sisfar_in_items_xml,true);
$criteria->compare('t.sisfar_in_items_xml_after_pre_process',$this->sisfar_in_items_xml_after_pre_process,true);
$criteria->compare('sisfar_in_items_received_xml_id',$this->sisfar_in_items_received_xml_id);
$criteria->compare('t.sisfar_in_items_ItemWeight',$this->sisfar_in_items_ItemWeight);
$criteria->compare('t.sisfar_in_items_RecptclId',$this->sisfar_in_items_RecptclId,true);
$criteria->compare('t.sisfar_in_items_CodCurrencyCd',$this->sisfar_in_items_CodCurrencyCd,true);
$criteria->compare('t.sisfar_in_items_Content',$this->sisfar_in_items_Content,true);
$criteria->compare('t.sisfar_in_items_LocalId',$this->sisfar_in_items_LocalId,true);
$criteria->compare('t.sisfar_in_items_ClassCd',$this->sisfar_in_items_ClassCd,true);
$criteria->compare('t.sisfar_in_items_AddService',$this->sisfar_in_items_AddService,true);
$criteria->compare('t.sisfar_in_items_OrigCountryCd',$this->sisfar_in_items_OrigCountryCd,true);
$criteria->compare('t.sisfar_in_items_DestCountryCd',$this->sisfar_in_items_DestCountryCd,true);
$criteria->compare('t.sisfar_in_items_PostalStatusCd',$this->sisfar_in_items_PostalStatusCd,true);
$criteria->compare('t.sisfar_in_items_Sender_Name',$this->sisfar_in_items_Sender_Name,true);
$criteria->compare('t.sisfar_in_items_Sender_Address',$this->sisfar_in_items_Sender_Address,true);
$criteria->compare('t.sisfar_in_items_Sender_City',$this->sisfar_in_items_Sender_City,true);
$criteria->compare('t.sisfar_in_items_Sender_Postcode',$this->sisfar_in_items_Sender_Postcode,true);
$criteria->compare('t.sisfar_in_items_AdditionalData',$this->sisfar_in_items_AdditionalData,true);
$criteria->compare('t.sisfar_in_items_Sender_CountrySubEntity',$this->sisfar_in_items_Sender_CountrySubEntity,true);
$criteria->compare('t.sisfar_in_items_Sender_PhoneNo',$this->sisfar_in_items_Sender_PhoneNo,true);
$criteria->compare('t.sisfar_in_items_Sender_FaxNo',$this->sisfar_in_items_Sender_FaxNo,true);
$criteria->compare('t.sisfar_in_items_Sender_Email',$this->sisfar_in_items_Sender_Email,true);
$criteria->compare('t.sisfar_in_items_Addressee_Name',$this->sisfar_in_items_Addressee_Name,true);
$criteria->compare('t.sisfar_in_items_Addressee_Forename',$this->sisfar_in_items_Addressee_Forename,true);
$criteria->compare('t.sisfar_in_items_Addressee_Address',$this->sisfar_in_items_Addressee_Address,true);
$criteria->compare('t.sisfar_in_items_Addressee_City',$this->sisfar_in_items_Addressee_City,true);
$criteria->compare('t.sisfar_in_items_Addressee_Postcode',$this->sisfar_in_items_Addressee_Postcode,true);
$criteria->compare('t.sisfar_in_items_Addressee_CountrySubEntity',$this->sisfar_in_items_Addressee_CountrySubEntity,true);
$criteria->compare('t.sisfar_in_items_Addressee_PhoneNo',$this->sisfar_in_items_Addressee_PhoneNo,true);
$criteria->compare('t.sisfar_in_items_Addressee_Email',$this->sisfar_in_items_Addressee_Email,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_TNCd',$this->sisfar_in_items_ItemEvent_TNCd,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_Date',$this->sisfar_in_items_ItemEvent_Date,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_OfficeCd',$this->sisfar_in_items_ItemEvent_OfficeCd,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_UserFid',$this->sisfar_in_items_ItemEvent_UserFid,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_NonDeliveryReason',$this->sisfar_in_items_ItemEvent_NonDeliveryReason,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_NonDeliveryMeasure',$this->sisfar_in_items_ItemEvent_NonDeliveryMeasure,true);
$criteria->compare('t.sisfar_in_items_ItemEvent_NextOfficeFcd',$this->sisfar_in_items_ItemEvent_NextOfficeFcd,true);
$criteria->compare('t.sisfar_in_items_Parcel_MailItemCategoryCd',$this->sisfar_in_items_Parcel_MailItemCategoryCd,true);
$criteria->compare('t.sisfar_in_items_Parcel_ExpressInd',$this->sisfar_in_items_Parcel_ExpressInd,true);
$criteria->compare('t.sisfar_in_items_Parcel_MailCategoryCd',$this->sisfar_in_items_Parcel_MailCategoryCd,true);
$criteria->compare('t.sisfar_in_items_Parcel_CoDInd',$this->sisfar_in_items_Parcel_CoDInd,true);
$criteria->compare('t.sisfar_in_items_Parcel_CoDValue',$this->sisfar_in_items_Parcel_CoDValue,true);
$criteria->compare('t.sisfar_in_items_Parcel_CoDCurrencyCd',$this->sisfar_in_items_Parcel_CoDCurrencyCd,true);
$criteria->compare('t.sisfar_in_items_Parcel_SenderInstructionCd',$this->sisfar_in_items_Parcel_SenderInstructionCd,true);
$criteria->compare('t.sisfar_in_items_Parcel_InsuredSDRValue',$this->sisfar_in_items_Parcel_InsuredSDRValue,true);
$criteria->compare('t.sisfar_in_items_Parcel_InsuredValue',$this->sisfar_in_items_Parcel_InsuredValue,true);
$criteria->compare('t.sisfar_in_items_Parcel_InsuredCurrencyCd',$this->sisfar_in_items_Parcel_InsuredCurrencyCd,true);
$criteria->compare('t.sisfar_in_items_Letter_MailCategoryCd',$this->sisfar_in_items_Letter_MailCategoryCd,true);
$criteria->compare('t.sisfar_in_items_Letter_CoDValue',$this->sisfar_in_items_Letter_CoDValue,true);
$criteria->compare('t.sisfar_in_items_Letter_InsuredCurrencyCd',$this->sisfar_in_items_Letter_InsuredCurrencyCd,true);
$criteria->compare('t.sisfar_in_items_Letter_MailItemCategoryCd',$this->sisfar_in_items_Letter_MailItemCategoryCd,true);
$criteria->compare('t.sisfar_in_items_Letter_LetterCharacteristicCd',$this->sisfar_in_items_Letter_LetterCharacteristicCd,true);
$criteria->compare('t.sisfar_in_items_Letter_InsuredValue',$this->sisfar_in_items_Letter_InsuredValue,true);
$criteria->compare('t.sisfar_in_items_Letter_InsuredSDRValue',$this->sisfar_in_items_Letter_InsuredSDRValue,true);
$criteria->compare('t.sisfar_in_items_Letter_CoDInd',$this->sisfar_in_items_Letter_CoDInd,true);
$criteria->compare('t.sisfar_in_items_Letter_ExpressInd',$this->sisfar_in_items_Letter_ExpressInd,true);
$criteria->compare('t.sisfar_in_items_mode',$this->sisfar_in_items_mode);
$criteria->compare('t.sisfar_in_items_created_by',$this->sisfar_in_items_created_by);
$criteria->compare('t.sisfar_in_items_created_ts',$this->sisfar_in_items_created_ts,true);
$criteria->compare('t.sisfar_in_items_modified_by',$this->sisfar_in_items_modified_by);
$criteria->compare('t.sisfar_in_items_modified_ts',$this->sisfar_in_items_modified_ts,true);
$criteria->compare('customers.sisfar_customers_name',$this->created_by);
$criteria->compare('customers.sisfar_customers_name',$this->modified_by);
$criteria->compare('t.sisfar_in_items_created_ts','>'.$this->created_from, true);
$criteria->compare('t.sisfar_in_items_created_ts','<'.$this->created_till, true);
$criteria->compare('t.sisfar_in_items_modified_ts','>'.$this->modified_from, true);
$criteria->compare('t.sisfar_in_items_modified_ts','<'.$this->modified_till, true);
if(Yii::app()->user->roles_id == 4) {
$limit = false;
$sql = 'select count(*) as count from sisfar_users_sources where sisfar_users_sources_users_id ='.Yii::app()->user->users_id;
$dataReader = Yii::app()->db->createCommand($sql);
$dataReader = $dataReader->query();
$row=$dataReader->read();
if($row['count'] > 0)
$limit = true;
if($limit) {
$criteria->with = array('customers', 'sources');
$criteria->scopes = 'limitedUser';
$criteria->together = true;
} else {
$criteria->with = array('customers');
}
} else {
$criteria->with = array('customers');
}
$criteria->together = true;
//$criteria->distinct=false;
//$criteria->count='COUNT(*)';
//$criteria->addBetweenCondition('sisfar_in_items_created_ts', $this->created_from, $this->created_till);
//$criteria->order = 'sisfar_in_items_xml_id DESC';
/*
if($this->action == 'csv') {
ob_clean();
Yii::app()->request->sendFile('test', 'testa_fails');
}
*/
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
'sort'=>array(
'defaultOrder'=>'t.sisfar_in_items_xml_id DESC',
'attributes'=>array(
'created_by'=>array(
'asc'=>'customers.sisfar_customers_name',
'desc'=>'customers.sisfar_customers_name DESC',
),
'*',
),
),
));
}
?>
controler
public function actionReceivedMailItems()
{
$model=new SisfarInItems2('search');
$model->unsetAttributes(); // clear any default values
if(isset($_GET['SisfarInItems2']))
$model->attributes=$_GET['SisfarInItems2'];
if(Yii::app()->request->getParam('export')) {
$this->actionExport($model);
Yii::app()->end();
}
$this->render('receivedMailItems',array(
'model'=>$model,
));
}
view
<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'sisfar-in-items2-grid',
'dataProvider'=>$model->receivedMailItems(),
'filter'=>$model,
'columns'=>array(
array(
'type' => 'raw',
'value' => 'CHtml::link($data->sisfar_in_items_xml_id, array("/Journal/mailItem", "mail_item"=>$data->sisfar_in_items_xml_id));',
'name' => 'sisfar_in_items_xml_id',
),
//'sisfar_in_items_xml_id',
'sisfar_in_items_ItemId',
//'sisfar_in_items_mode',
array(
'type' => 'raw',
'value' => 'CHtml::link($data->sisfar_in_items_mode, array("/conf/admin#receifedPackageStatus"));',
'name' => 'sisfar_in_items_mode',
),
array(
'name' => 'created_by',
'value' => '$data->customers->sisfar_customers_name',
),
'sisfar_in_items_created_ts',
array(
'name' => 'modified_by',
'value' => '$data->customers->sisfar_customers_name',
),
'sisfar_in_items_modified_ts',
),
));
?>