Many to Many Searching and Sorting

hi i have followed the

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

with table structure :

Products

__________

productID

Name

enable




ProductsCategories

__________________

categoryID

productID




categories

____________

categoryID

name

parentID

model code is :

class Product extends CActiveRecord {


    public $categoriez = array();

    public $categories_search;

.....

     public function relations() {

        

        return array(

            'categories' => array(self::MANY_MANY, 'Category', 'Productscategories(productID,categoryID)'),

....




 public function search() {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.


        $criteria = new CDbCriteria;

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

        $criteria->compare('Name', $this->productName, true);

        $criteria->with = array('categories');

        $criteria->compare('categories.category.categoryName', $this->categoriez, true);

}

class Productscategories extends CActiveRecord

{

...

  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(

			'product' => array(self::BELONGS_TO, 'Products', 'productID'),

			'category' => array(self::BELONGS_TO, 'Categories', 'categoryID'),

		);

	}

class Category extends CActiveRecord {

...

public function relations() {

       

        return array(

            'products' => array(self::MANY_MANY, 'Product', 'Productscategories(categoryID,productID)'),

            

        );

    }

...

and i m getting error :

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories.category.categoryName' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`productID`) FROM `products` `t`  LEFT OUTER JOIN `Productscategories` `categories_categories` ON (`t`.`productID`=`categories_categories`.`productID`) LEFT OUTER JOIN `categories` `categories` ON (`categories`.`categoryID`=`categories_categories`.`categoryID`)  WHERE (categories.category.categoryName=:ycp0)

please guide what i m missing here .

main problem is that

$criteria->with = array('categories');

$criteria->compare('categories.category.categoryName', $this->categoriez, true);

will not give right results. To make it work you should add ‘together’=>true to relation:

with=array('categories'=>array('together'=>true));

this way Yii will use join instead of two separate queries. Then you should refer to target table like this:

$criteria->compare('categories.categoryName', $this->categoriez, true);

…but - results can be different than expected ( $object->categories will return only categories that matches such criteria ). You could instead use SQL "exists":

$criteria->addCondition('EXISTS( SELECT * FROM categories INNER JOIN categories_pivot ON (categories_pivot.id_category = categories.id) WHERE categories_pivot.id_model = t.id AND categories.categoryName LIKE :categoryName )');

$criteria->params[':categoryName'] = $this->categoriez;

or something like this. In such case ‘with’ and ‘together’ is not necessary.

sadafs, your code now working? What method did you use?