[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!