Hey redguy, thanks for responding. Coincidentally, I am making heavy use of your wiki on searching/sorting by related tables!
My database is MySQL, and the storage engine for all three tables is MyISAM. I guess that is the default on my hosting provider (HostGator).
Code:
Model:
class Customers extends CActiveRecord
public $statusSearch=array();
public $budget_search;
public $size_search;
public $deadline_search;
public $source_search;
public $neighborhood_search;
public $anyAll='AND';
public $sortBy='t.last';
public $colSelect='*';
public $leadsSelect='*';
public $searchSelect='*';
...
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->select=$this->colSelect;
$criteria->together=true;
$criteria->with=array(
'leads'=>array('select'=>$this->leadsSelect),
'searches'=>array('select'=>$this->searchSelect)
);
$criteria->order=$this->sortBy . ' ASC';
$criteria->compare('id',$this->id,false,$this->anyAll);
$criteria->compare('last',$this->last,false,$this->anyAll);
$criteria->compare('pref',$this->pref,false,$this->anyAll);
$criteria->compare('first',$this->first,false,$this->anyAll);
$criteria->compare('middle',$this->middle,false,$this->anyAll);
$criteria->compare('suf',$this->suf,false,$this->anyAll);
$criteria->compare('email',$this->email,false,$this->anyAll);
$criteria->compare('phone',$this->phone,false,$this->anyAll);
$criteria->compare('preferred',$this->preferred);
$criteria->compare('status',$this->status);
$criteria->compare('home',$this->home,false,$this->anyAll);
$criteria->compare('notes',$this->notes,false,$this->anyAll);
$criteria->compare('agent',$this->agent,false,$this->anyAll);
$criteria->compare('agnt_x',$this->agnt_x,false,$this->anyAll);
$criteria->compare('createdDate',$this->createdDate,false,$this->anyAll);
$criteria->compare('upDated',$this->upDated,false,$this->anyAll);
$criteria->compare('searches.budget',$this->budget_search,false,$this->anyAll);
$criteria->compare('searches.size',$this->size_search,false,$this->anyAll);
$criteria->compare('searches.deadline',$this->deadline_search,false,$this->anyAll);
$criteria->compare('searches.neighborhood',$this->neighborhood_search,false,$this->anyAll);
$criteria->compare('leads.source',$this->source_search,false,$this->anyAll);
$criteria->addInCondition('status',$this->statusSearch);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
'pagination'=>array('pageSize'=>20),
));
}
Controller:
public function actionAdmin()
{
$model=new Customers('search');
$model->unsetAttributes(); // clear any default values
if(isset($_GET['Customers']))
$model->attributes=$x=$_GET['Customers'];
if($model->budget_search) $model->budget_search=$x['budgetOpr'] . $model->budget_search;
if($model->size_search) $model->size_search=$x['sizeOpr'] . $model->size_search;
if($model->deadline_search) $model->deadline_search=$x['deadlineOpr'] . $model->deadline_search;
$model->colSelect='t.status,t.last,t.first,t.email,t.phone,t.agent';
$model->leadsSelect='leads.source,leads.dateContact';
$model->searchSelect='searches.budget,searches.size,searches.deadline,searches.neighborhood';
$this->render('admin',array(
'model'=>$model,
));
}
SQL from the log (this is the query generated with no search terms:
Querying SQL: SELECT `t`.`status` AS `t0_c9`, `t`.`last` AS `t0_c1`,
`t`.`first` AS `t0_c3`, `t`.`email` AS `t0_c6`, `t`.`phone` AS `t0_c7`,
`t`.`agent` AS `t0_c12`, `t`.`id` AS `t0_c0`, `leads`.`source` AS `t1_c7`,
`leads`.`dateContact` AS `t1_c4`, `leads`.`id` AS `t1_c0`,
`searches`.`budget` AS `t2_c4`, `searches`.`size` AS `t2_c6`,
`searches`.`deadline` AS `t2_c7`, `searches`.`neighborhood` AS `t2_c9`,
`searches`.`id` AS `t2_c0` FROM `customers` `t` LEFT OUTER JOIN `leads`
`leads` ON (`leads`.`personId`=`t`.`id`) LEFT OUTER JOIN `searches`
`searches` ON (`searches`.`personId`=`t`.`id`) WHERE (0=1) ORDER BY t.last
ASC LIMIT 20
Thanks!