[Yii Framework] About the MANY_MANY

Let say we have three table as:

product:
　　id;
　　name;
category:
　　id;
　　name;
category_product:
　　category_id;
　　product_id;

From their structure we can know that product table and category table are MANY_MANY relation, category_product is the middle table, then how can we get the product records when we have a category.id? 

Set the models

Product: 

 

class Product extends CActiveRecord
{
    //other codes
    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(
            'categories'=>array(
                self::MANY_MANY,
                'Category',
                'category_product(category_id, product_id)'
            ),
            'category_product'=>array(
                self::HAS_MANY,
                'CategoryProduct',
                'product_id'
            ),
        );
    }
    //other codes
}

 Category:

class Category extends CActiveRecord
{
    //other codes
    public function relations()
    {
        return array(
            'products'=>array(
                self::MANY_MANY,
                'Product',
                'category_product(category_id, product_id)',
            ),
            'category_product'=>array(
                self::HAS_MANY,
                'CategoryProduct',
                'category_id',
            ),
        );
    }
    //other codes
}

 CategoryProduct:

class CategoryProduct extends CActiveRecord
{
    //other codes
    public function relations()
    {
        return array(
            'category'=>array(
                self::BELONGS_TO,
                'Category',
                'category_id',
            ),
            'product'=>array(
                self::BELONGS_TO,
                'Product',
                'product_id',
            ),
        );
    }
    //other codes
}

Ok, let's show how to get the records in a controller!

Class ProductController extends ActiveRecord
{
    public function actionIndex()
    {
        $dataProvider=new CActiveDataProvider(
            'Product',
            array(
                'criteria'=>array(
                    'with'=>array('category_product'),
                    'condition'=>'display=1 AND category_product.category_id=1',
                    'order'=>'t.id DESC',
                    'together'=>true,
                ),
            )
        );
        $this->render('index',array(
            'dataProvider'=>$dataProvider,
        ));
    }
} 

Please pay attention to the property "together", we have to set it to true, otherwise, we will get error, of course, you can have a try. :)

By the way, we can also get the records with these codes without setting the rules in models!

$dataProvider=new CActiveDataProvider(
            'Product',
            array(
                'criteria'=>array(
                    'condition'=>'display=1 AND cp.category_id='.$_GET['cat'],
                    'order'=>'t.id DESC',
                    'join'=>'LEFT JOIN category_product cp ON cp.product_id = t.id',
                ),
            )
        );

Now we have another scene, we have a productID from the request, and we want to get all the same category products of this productID, let's check these code:

$criteria = new CDbCriteria;
$criteria->with = array('category_product');
$criteria->condition = 'category_id in (select category_id from category_product where product_id = 1)';
$criteria->together = true;
$models = Product::model()->findAll($criteria);

Have fun with Yii!