Relations Not Working In Many_Many Relationship

Hi, I’m using an intermediate “join” table to establish a MANY_MANY relationship for the first time, and I find that when I use the relation name in a query, it is not recognized. Here’s my scenario:

I have a User model and a Project model, and the intermediate join model is the UserProject model. The UserProject model just has the user_id and project_id. The relation names should be ok:

In the User.php model I have the relation:

‘projects’ => array(self::MANY_MANY, ‘Project’, ‘user_project(user_id, project_id)’)

and in the Project.php model I have:

‘users’ => array(self::MANY_MANY, ‘User’, ‘user_project(project_id, user_id)’)

But in the actionIndex() method of the ProjectController, I have simply:

$dataProvider=new CActiveDataProvider(‘Project’, array(

'criteria'=>array(


'with'=>array('users'),

)));

But the join is not made, the relation name “users” is not recognized even though it’s declared in the Project model. Can someone tell me what I’m missing?

Regards,

Larry

If I may ask, what is the purpose of the UserProject model and by any chance did you change the relationship code of your Project/User model?

The UserProject model joins the user table and the project table in the database, using only the id’s of the two tables. It has only the fields user_id and project_id. I have not modified the relationships in either the User model or the Project model.

Larry

I don’t think the ‘with’ in the criteria property joins the two table. You can join the table by adding ‘join’ statement. You can look this up for more information:

Try to verify if the values inside your arrays are the valid format.

Refer to this documentation for setting up relations.

Thanks for sharing.

Yay I got it!

What I was trying to do was list all of the projects that belong to one artist, using a CActiveDataProvider in the ProjectController’s actionIndex(). I just needed to add the ‘together’=>true parameter, like so:


                $dataProvider=new CActiveDataProvider('Project', array(

                        'criteria'=>array(

                                'with'=>array('users', 'projectType'),

                                'condition'=>'year=2014 and users.id = 2',

                                'together'=>true,

                        ),

Thanks everybody, my original question was misleading. In fact the users relation works without specifying it in the 'with" parameter. I was unable to use ‘users.id’ without the ‘together’=>true parameter.

Thanks for the help.

Larry

Nope, it’s not working as I would like. With the code above, it only returns a single user, when there are multiple users associated with a given project. Does anyone know what I’m still missing?

Larry

Have you tried using CActiveRecord?

Well in the end, I found a solution, but it is not what I had expected. In order to obtain an ordered list of users belonging to a single project, I did this:


$project=Project::model()->with('users')->findByPk($data->id, array(

        'order'=>'users.last_name, users.first_name ASC'

));


$artist_list = array();


foreach($project->users as $artist) {

        $artist_list[] = $artist->first_name . " " . $artist->last_name;

}

The foreach loop uses the "users" relation to obtain the associated users (artists).

This is my first experience using an intermediate join table to establish a MANY_MANY relation. There must be an easier way, I’m not done with this yet.

Larry

I think the CActiveRecord IS the easiest way possible to get the data. have you tried using this syntax:


$project = Project::model()->with('users')->findAll();