Setting Up Yii MANY_MANY Relationship

3
votes

I am trying to set up a MANY_MANY Relationship in Yii using Active Record.

I have three tables

profile profile_id profile_description

category category_id category_name

profile_category profile_id category_id

My models are Profile, Category, and ProfileCategory.

I am trying to run a query using the category_id that will pull up all of the profiles that are in that category.

This is the information in the category model. 

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(
        'profiles'=>array(
            self::MANY_MANY,
            'Profile',
            'profile_category(category_id, profile_id)',
        ),
        'profile_category'=>array(
            self::HAS_MANY,
            'ProfileCategory',
            'category_id',
        ),
    );
}

Profile Model

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',
            'profile_category(profile_id, category_id)'
        ),
        'profileCategory'=>array(
            self::HAS_MANY,
            'ProfileCategory',
            'profile_id'
        ),
   );
}

ProfileCategory Model

public function relations()
{
    return array(
        'category'=>array(
            self::BELONGS_TO,
            'Category',
            'category_id',
        ),
        'profile'=>array(
            self::BELONGS_TO,
            'Profile',
            'profile_id',
        ),
    );
}

Controller

public function actionResults()
{   
    $category=$_POST['terms'];
    $dataProvider=new CActiveDataProvider(
        'Profile',
        array(
            'criteria'=>array(
                'with'=>array('profile_category'),
                'condition'=>'display=10 AND profile_category.category_id=1',
                'order'=>'t.id DESC',
                'together'=>true,
            ),
        )
    );
    $this->render('results',array(
        'dataProvider'=>$dataProvider,
    ));
}

View

<div id=resultsleft>
<?php
foreach($dataProvider as $value)
{
echo $value->profile_id;
}
?>
</div>

Any thoughts? Thank You! Nothing shows in the view.

2
phpmysqlactiverecordyii

2 Answers

2
votes

It would be better for logical id first to model relation...

An example is available here.

Category model

'Profile', 'profile_category(profile_id, ...)'

public function relations()
{
    return array(
        'profiles'=>array(
            self::MANY_MANY,
            'Profile',
            'profile_category(profile_id, category_id)'
        ),
        'profile_category'=>array(
            self::HAS_MANY,
            'ProfileCategory',
            'category_id',
        ),
    );
}

Profile model

'Category', 'profile_category(category_id, ...)'

public function relations()
{
    return array(
        'categories'=>array(
            self::MANY_MANY,
            'Category',
            'profile_category(category_id, profile_id)'
        ),
        'profile_category'=>array(
            self::HAS_MANY,
            'ProfileCategory',
            'profile_id'
        ),
    );
}

Its actualy if model of database has
many self::BELONGS_TO and database has only PK (PrimaryKeys)

such code will be correctly executed

print_r(Profile::model()->findByPk(5)->categories);
print_r(Category::model()->findByPk(8)->profiles);
2
votes

You have to set up a property called profileCategory (not profile_category) in the profile model:

    'profileCategory'=>array(
        self::HAS_MANY,
        'ProfileCategory',
        'profile_id'
    ),

You can use it with an and condition as follows:

       'criteria'=>array(
            'with'=>array('profileCategory'),
            'condition'=>'display=10 AND profileCategory.category_id=1',
            'order'=>'t.id DESC',
            'together'=>true,
        ),