Column 'id' in field list is ambiguous in

Question

I am new in YII. Trying to implement the query in controller, Below is my code which has taken from controller.

public function getCommentListByUser($user_id)
{
        $criteria = new CDbCriteria;
        $criteria->select = "username";
        $criteria->alias = "u";
            $criteria->condition = 'cmt.commented_userd_id  = '.$user_id;
            $criteria->join = 'JOIN tbl_taskcomment cmt ON  (cmt.commented_userd_id = u.id)';
            $criteria->order = 'cmt.id ASC';  
            $model = User::model()->findAll($criteria);  
            return $model;
}

when i ran the page, I got the following error,

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous. The SQL statement executed was: SELECT id, username, email, createtime, lastvisit, superuser, status FROM tbl_users u JOIN tbl_taskcomment cmt ON (cmt.commented_userd_id = u.id) WHERE cmt.commented_userd_id = 1 ORDER BY cmt.id ASC

Instead fetching username from USER table,it comes with all column with out alias name. I knew this issue related with alias name. What i done wrong on this syntax as well as code.

Kindly advice

Answer 1

You maybe have id column in both the tables. If you need columns from table tbl_users you should use alias to get each column:

SELECT u.id, u.username, u.email, u.createtime, u.lastvisit, u.superuser, u.status FROM tbl_users u

And your WHERE condition should be:

WHERE u.id = 1 

Answer 2

Either

change: $criteria->select = "username" to ==> $criteria->select = "u.username"

OR

remove: $criteria->alias = "u" and change $criteria->select = "username" to ==> $criteria->select = "t.username" and change all left join alias 'u.field_name' to ==> 't.field_name'

Answer 3

I guess I faced some problems with aliases in CDbCriteria.
Can you try default alias (which is t)?

Answer 4

This should help you:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#disambiguating-column-names