Many_Many Relationship And Cactivedataprovider

Yii 1.1 General Discussion
1

Hello. I have these tables: tbl_user, tbl_project and tbl_user_project. So i need to get all projects (from Project AR class) but only those projects where the logged-in user is in, showing it on a CActiveDataProvider, but it is not working, i do not really understand this issue:


$dataProvider=new CActiveDataProvider('Project',

            array(

                'criteria'=>array(

                    'criteria'=>array(

                    'condition'=>Project::model()->with('users')->findAll('user_id=:userId', array(':userId'=>Yii::app()->user->id)),

And the relations (Project AR class):




            'users' => array(self::MANY_MANY, 'UserProjJunction', 'tbl_user_proj_junction(user_id, project_id)'),

This is the error that i get:

[color="#FF0000"]CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘user_id’ in where clause is ambiguous. The SQL statement executed was: SELECT t.id AS t0_c0, t.project_name AS t0_c1, t.description AS t0_c2, t.start_date AS t0_c3, t.end_date AS t0_c4, t.category_id AS t0_c5, users.user_id AS t1_c0, users.project_id AS t1_c1 FROM tbl_project t LEFT OUTER JOIN tbl_user_proj_junction users_users ON (t.id=users_users.user_id) LEFT OUTER JOIN tbl_user_proj_junction users ON (users.user_id=users_users.project_id) WHERE (user_id=:userId)[/color]

2

It’s saying that the field user_id appears in more than one table in your query, so you need to prepend the table name to let it know which one to use.

Try:




->findAll('users.user_id=:userId', array('userId'=>Yii::app()->user->id)),
3

Oh thanks, now i get the following:

[color="#FF0000"]Array to string conversion[/color]

4

Anybody?

5

Do you have any more error details?

6

okay I see you doing it wrong

try this


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

	'criteria'=>array(

		'with'=>'users',

		'condition'=>'user_id=:userID', 

		'params'=>array(':userID'=>Yii::app()->user->id)

	)

));
7

Fixing the ‘with’ condition to: users.user_id (since ‘user_id’ it’s ambiguous), i get:

[color="#FF0000"]CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘users.user_id’ in ‘where clause’. The SQL statement executed was: SELECT t.id AS t0_c0, t.project_name AS t0_c1, t.description AS t0_c2, t.start_date AS t0_c3, t.end_date AS t0_c4, t.category_id AS t0_c5 FROM tbl_project t WHERE (users.user_id=:userId) LIMIT 10[/color]

8

just use it as it don’t add “users.user_id” leave it as user_id and it should work

9

[color="#FF0000"]CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘user_id’ in where clause is ambiguous. The SQL statement executed was: SELECT COUNT(DISTINCT t.id) FROM tbl_project t LEFT OUTER JOIN tbl_user_proj_junction users_users ON (t.id=users_users.user_id) LEFT OUTER JOIN tbl_user_proj_junction users ON (users.user_id=users_users.project_id) WHERE (user_id=:userId)[/color]

10

try "users_users.user_id"

also paste your table names and code how you have it in your application in you original question

you have your tables like so


 tbl_user, tbl_project and tbl_user_project

where does this "tbl_user_proj_junction" come form