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:
[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_projectt LEFT OUTER JOIN tbl_user_proj_junctionusers_users ON (t.id=users_users.user_id) LEFT OUTER JOIN tbl_user_proj_junctionusers ON (users.user_id=users_users.project_id) WHERE (user_id=:userId)[/color]
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.
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_projectt WHERE (users.user_id=:userId) LIMIT 10[/color]
[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_projectt LEFT OUTER JOIN tbl_user_proj_junctionusers_users ON (t.id=users_users.user_id) LEFT OUTER JOIN tbl_user_proj_junctionusers ON (users.user_id=users_users.project_id) WHERE (user_id=:userId)[/color]