CGridView Relational Search Doesn't Work

Hi everyone.

Here is the case :

I have a CActiveRecord named Book and Book has SELF::MANY relations with Author.

Here is the Book model :




class Book extends BangdaAR

{

        public $author;


	public function rules()

	{

		return array(

			array('author', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		return array(

                    'authors' => array(self::HAS_MANY, 'Author', 'book_id'),

		);

	}


	public function search()

	{

		$criteria=new CDbCriteria;


                $criteria->compare('name',$this->author,true);

                $criteria->with = array('authors'=>array('select'=>'name'));

                

		return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

		));

	}

}




And I have put the attribute ‘name’ in Author as a safe attribute.

Now when I search using this, there is error shown :




Error 500: <h1>CDbException</h1>

<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22] [1054] Unknown column 'name' in 'where clause'. The SQL statement executed was: SELECT `t`.`book_id` AS `t0_c0`, `t`.`book_title` AS `t0_c1`... FROM `tbl_buku` `t`  WHERE (name LIKE :ycp0) LIMIT 10



Am I implementing it right ? I follow this Search as Text Input For a Foreign Key Field in CGRidView

Thanks

Hi,

you should to disambiguate column names in relational queries:




...

            	$criteria->compare('authors.name',$this->author,true);

                $criteria->with = array('authors'=>array('select'=>'authors.name'));


...



Thanks for the reply =)

I tried your suggestion but still not working. It is still showing the same error message :(

Could you please provide full SQL query that fails after suggested corrections ?

Found the sollution :

$criteria->compare(‘authors.name’,$this->authors, true);

$criteria->with = array(‘authors’=>array(‘select’=>‘authors.name’,‘together’=>true));

I dont know but it should use ‘together’ => true. But I still dont understand why and how ‘together’ works. Anyone can explain why should use ‘together’=>true ? Thanks so much

First you should read and realize this.

Now in you case if together is not set to true - records from table authors will joined with books using second (separate) SQL query. But in you main query you have included refernce to this table (authors.name) which apeear in WHERE clause, but JOIN statement will not appear because together is not true. This is the reason for "Unknown column …" error. Setting together to true you are forcing Yii to include JOIN statement to main SQL query - so now you can use field references from authors table[i].

[/i]

Hope this will help you. Cheers.

Wow,thanks a lot :)

But the same problem accured again when I’m using 2 method like that :




$criteria->compare('addedTitles.added_title',$this->addedTitleVar, true);

$criteria->with = array('addedTitles'=>array('select'=>'addedTitles.added_title','together'=>true));

                

$criteria->compare('collations.collation',$this->collation, true);

$criteria->with = array('collations'=>array('select'=>'collations.collation','together'=>true));



Any insight for this ?

By assigning $criteria->with second time you had overriden its previous value. In this case you should use with as array:




...

$criteria->compare('addedTitles.added_title',$this->addedTitleVar, true);  

$criteria->compare('collations.collation',$this->collation, true);


$criteria->with = array(

  'addedTitles'=>array('select'=>'addedTitles.added_title','together'=>true),

  'collations'=>array('select'=>'collations.collation','together'=>true),

);

...



If I use more than one methods, ex :




$criteria->compare('addedTitles.added_title',$this->addedTitleVar, true);

$criteria->with = array('addedTitles'=>array('select'=>'addedTitles.added_title','together'=>true));

                

$criteria->compare('collations.collation',$this->collation, true);

$criteria->with = array('collations'=>array('select'=>'collations.collation','together'=>true));

                

$criteria->compare('imprints.imprint',$this->imprint, true);

$criteria->with = array('imprints'=>array('select'=>'imprints.imprint','together'=>true));



This will show error message like :




 Unknown column 'addedTitles.added_title' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`cd_id`) FROM `tbl_cd` `t`  LEFT OUTER JOIN `tbl_imprint` `imprints` ON (`imprints`.`cd_id`=`t`.`cd_id`)  WHERE (addedTitles.added_title LIKE :ycp0)



SO it seems that yii will join only the latest declared method, in this case is the tbl_imprint

I’m so sorry, it is my mistake in declaring the relation ‘with’.

It should be done like this :





$criteria->with = array(

                        'addedTitles'=>array(

                                'select'=>'addedTitles.added_title','together'=>true

                        ),

                        'collations'=>array(

                                'select'=>'collations.collation','together'=>true

                        ),


                    );



Thanks :D