Thanks to all the postings on the subject, I can successfully do a CGridView search on a field in a related table.
Works great… almost!
I noticed that the search is ALWAYS ‘running’ even though no criteria has been entered in the CGridView’s search box. By this, I mean the query ALWAYS has a WHERE clause that it is trying to satisfy even if I have not entered anything inCGridView’s search boxes but the query being generated has to be incorrect because without the WHERE clause, I should have 5,278 records but this query is returning 2,763 records!?!?!?
Here is the output of my CDbCriteria object:
CDbCriteria Object ( [select] => * [distinct] => [condition] => (author.last_name LIKE :ycp0) AND (publisher.pub_co LIKE :ycp1) [params] => Array ( [:ycp0] => %% [:ycp1] => %% ) [limit] => -1 [offset] => -1 [order] => [group] => [join] => [having] => [with] => Array ( [0] => author [1] => publisher ) [alias] => [together] => [_e:CComponent:private] => [_m:CComponent:private] => )
Here is the query it is running:
SELECT t.id AS t0_c0, t.title AS t0_c1,
t.subtitle AS t0_c2, t.year AS t0_c3, t.pgs AS t0_c4,
t.ddn AS t0_c5, t.call_ltrs AS t0_c6, t.an AS t0_c7,
t.pub_id AS t0_c8, t.auth_id AS t0_c9, t.coauth_id AS
t0_c10, t.isbn AS t0_c11, t.lccn AS t0_c12, t.media_id AS
t0_c13, t.recID AS t0_c14, t.user_id AS t0_c15,
t.created_at AS t0_c16, t.updated_at AS t0_c17,
t.updated_by AS t0_c18, author.id AS t1_c0,
author.last_name AS t1_c1, author.first_name AS t1_c2,
author.full_name AS t1_c3, author.auth_year AS t1_c4,
publisher.id AS t2_c0, publisher.pub_co AS t2_c1,
publisher.city AS t2_c2, publisher.state AS t2_c3,
publisher.country AS t2_c4 FROM book t LEFT OUTER JOIN author
author ON (t.auth_id=author.id) LEFT OUTER JOIN publisher
publisher ON (t.pub_id=publisher.id) WHERE ((author.last_name
LIKE :ycp0) AND (publisher.pub_co LIKE :ycp1)
Although "ycp0" and "ycp1" should be effectively empty, I am seeing about half the records that I would see if the WHERE clause was NOT there.
Here is the dataModel my CGridView is being told to use:
public function Relsearch()
{
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id,true);
$criteria->compare('title',$this->title,true);
$criteria->compare('subtitle',$this->subtitle,true);
$criteria->compare('year',$this->year,true);
$criteria->compare('pgs',$this->pgs);
$criteria->compare('ddn',$this->ddn,true);
$criteria->compare('call_ltrs',$this->call_ltrs,true);
$criteria->compare('an',$this->an,true);
$criteria->compare('coauth_id',$this->coauth_id,true);
$criteria->compare('isbn',$this->isbn,true);
$criteria->compare('lccn',$this->lccn,true);
$criteria->compare('media_id',$this->media_id);
$criteria->compare('recID',$this->recID,true);
$criteria->compare('user_id',$this->user_id);
$criteria->compare('created_at',$this->created_at,true);
$criteria->compare('updated_at',$this->updated_at,true);
$criteria->compare('updated_by',$this->updated_by);
$criteria->with=array('author','publisher');
$criteria->addSearchCondition('author.last_name',$this->auth_id,true);
$criteria->addSearchCondition('publisher.pub_co',$this->pub_id,true);
return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria,
)
);
}
I guess what I am asking is:
Is this a Yii bug?
If not, how do I make the adding of the SearchConditions conditional so that they only get incorporated when the search field is NOT empty (and hence show me ALL my records when no Search parameters are entered for the CGridView)?
I just gained some more information. The query is doing the equivalent of 'SELECT all records WHERE auth_id iS NOT NULL AND pub_id IS NOT NULL.
This accounts for my missing records.
So I am back to wanting to know how to make the $criteria->addSearchCondition() only add the the condition if the GridView Search box has something in it… OR tell the grid to display the records with null values for auth_id or pub_id???