CDbCriteria select ambiguous columns issue

Hi everybody,

I’ve got this issue. :P

I’ve got two tables, flk_users and flk_cards linked by a ONE-TO-MANY flk_users.id-flk_cards.user_id relation.

They both have a ‘status’ int(1) field too.

I would like to implement the SQL:


SELECT u.id, u.username FROM `flk_users` `u` JOIN flk_cards c ON u.id = c.user_id ORDER BY u.username ASC

with AR and CDbCriteria inside a FlkUsers class method.

Here’s my code:


$criteria = new CDbCriteria;

$criteria->alias = 'u';

$criteria->select= 'u.id,u.username';

$criteria->join= 'JOIN flk_cards c ON u.id = c.user_id';

$criteria->order=' u.username ASC';

$users = self::model()->findAll($criteria);

I get this error:


CDbCommand ha riportato un errore nell'esecuzione della query SQL: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in field list is ambiguous. The SQL statement executed was: SELECT id, username, email, createtime, lastvisit, superuser, status, u.id, u.username FROM `flk_users` `u` JOIN flk_cards c ON u.id = c.user_id ORDER BY u.username ASC

It seems the ‘status’ field is ambiguous… :huh:

Now, three questions here:

Why does the generated SQL include all of the flk_users fields, even though I set only two of them in


$criteria->select= 'u.id,u.username'

? I guess if it did not include the ‘status’ field I would not receive the error…

What does ‘Column ‘status’ in field list is ambiguous’ mean?

What am I missing and how could I solve this? ???

Thank you very much!

Andrea

interesting, make a default scope, i think it should help u:


function defaultScope()

    {

        return array(

            'alias' => $this->tableName()       

    }

thank you elbek, the solution was in the defaultScope method! ;D

It was not in defining the ‘alias’ properties, though :rolleyes:

I had not noticed the defaultScope() method before (I borrowed this class from the web), but it was already implemented and I saw it defined a ‘select’ attribute which was responsible for reading all of the table fields.

Eliminating the ‘select’ attribute, no ‘status’ field was any more selected and no ambiguous error was thrown.

Thank you all guys, and sorry for my novice missings :P