CODEWITHSUNDEEP
phpmysqlyii
mymotherland
mymotherland

Reputation: 8248

Column 'id' in field list is ambiguous in YII

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

Upvotes: 1

Views: 9839

Answers (4)

Darshit Gajjar
Darshit Gajjar

Reputation: 711

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'

Upvotes: 3

Campinho
Campinho

Reputation: 452

This should help you:

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

Upvotes: 2

yuvin
yuvin

Reputation: 383

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

Upvotes: 0

Muhammad Zeeshan
Muhammad Zeeshan

Reputation: 8856

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

Upvotes: 0

Related Questions