Yii2: Unique validator doesn't prefix attribute name with model's database table name

[sepidemahmoodi]

Hi,
this rule is in page model rules :
[['code'], 'unique']
and code attribute exist in post model.

I join two table in page::find():

$query = new ActiveQuery(get_called_class()); $query->joinWith('post');

then ambiguous error accured on same code attribute.

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'code' in where clause is ambiguous 
The SQL being executed was: 
SELECT EXISTS(SELECT page.* FROM page LEFT JOIN post ON page.postId=post.id AND ((code='12')))

it seems in
yii\validators\UniqueValidator::prepareConditions($targetAttribute, $model, $attribute)
adding table name as prefix while preparing conditions solve this problem.

[samdark]

Which version of Yii are you using?

[yii-bot]

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

_This is an automated comment, triggered by adding the label status:need more info._

[sepidemahmoodi]

i'm using 2.0.11.2 version of yii2

[samdark]

Can you check with code from master branch?

[sepidemahmoodi]

I tried with code from master but ambiguous error has accured again

[samdark]

OK. We need table structure and some data to reproduce it.

[samdark]

Model as well.

[sepidemahmoodi]

I have a college table with this structure :

$this->createTable('college', [
            'id' => $this->primaryKey(),
            'code' => $this->string()->notNull(),
            'createdAt' => $this->integer(),
            'updatedAt' => $this->integer(),
   ]);

that college information stored in college_info table :

$this->createTable('college_info', [
            'id' => $this->primaryKey(),
            'name' => $this->string(),
            'languageId' => $this->string(),
            'description' => $this->text(),
            'collegeId' => $this->integer()->notNull(),
            'websiteId' => $this->integer(),
            'url' => $this->text(),
    ]);

each college has many department and that structure is :

$this->createTable('department', [
            'id' => $this->primaryKey(),
            'code' => $this->string()->notNull(),
            'collegeId' => $this->integer()->notNull(),
            'createdAt' => $this->integer(),
            'updatedAt' => $this->integer(),
   ]);

department information stored in department_info :

$this->createTable('department_info', [
            'id' => $this->primaryKey(),
            'name' => $this->string(),
            'languageId' => $this->string(),
            'description' => $this->text(),
            'departmentId' => $this->integer()->notNull(),
        ]);

each depatment has many major and major table structure is :

$this->createTable('major', [
            'id' => $this->primaryKey(),
            'code' => $this->string()->notNull(),
            'degree' => $this->integer()->notNull().' DEFAULT 3',
            'departmentId' => $this->integer()->notNull(),
            'createdAt' => $this->integer(),
            'updatedAt' => $this->integer(),
      ]);

and major information stored in major_info table :

$this->createTable('major_info', [
            'id' => $this->primaryKey(),
            'name' => $this->string(),
            'languageId' => $this->string(),
            'description' => $this->text(),
            'majorId' => $this->integer()
     ]);

department and major implement in one module and college is in another module.

I override Department::find() that in special conditon join with college and college_info table.
in this query ambiguous error accured.

class Department extends baseDepartment
{
    public function rules()
    {
        return [
            [['code', 'collegeId'], 'required'],
            [['code'], 'unique'],
            [['code'], 'integer'],
        ];
    }
    public function transactions()
    {
        return [
            self::SCENARIO_DEFAULT => self::OP_ALL,
        ];
    }
    public static function find()
    {
        $query = new ActiveQuery(get_called_class());
        if(Yii::$app->user->isSuperuser()){
            return $query;
        }
        $query->joinWith('college')
            ->leftJoin('college_info', 'college_info.collegeId = college.id')
            ->andWhere('college_info.websiteId = ' . Yii::$app->website->id);
        return $query;
    }

[samdark]

The issue seems to be about unique validator not prefixing field name with model's table alias.

[sepidemahmoodi]

this changes is solution?

[samdark]

@sepidemahmoodi works for you?

[sepidemahmoodi]

@samdark , this error was accured -_-

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous
The SQL being executed was: SELECT `id` FROM `department` LEFT JOIN `college` ON `department`.`collegeId` = `college`.`id` LEFT JOIN `college_info` ON college_info.collegeId = college.id WHERE (college_info.websiteId = 18) AND ((`department`.`code`='14')) LIMIT 2

department prefix added to code but id is ambiguous now

[samdark]

@vladis84 ↑

[sepidemahmoodi]

I apply all changes that you do in unique validator
it seems problem of validator is solved and ambiguous error on id attribute related to my query.
is'nt it?

[samdark]

Seems so.

[sepidemahmoodi]

then first problem was solved,
These changes will be added to the framework?

[samdark]

Yes, sooner or later. Depends on @vladis84. The pull request needs unit tests.

[sepidemahmoodi]

Ok,
thanks a lot. :)))

[jlorente]

I think the resolution of this Issue has caused a problem with query aliases. If someone overrides the find() method of the model and uses it to add and alias to the model table, the validator will execute a query that will cause an error.

Maybe the prefix used on the validator query should be extracted from the Query object and not from the tableName() method.

[samdark]

@jlorente probably you're right. If you have time to improve it, create a pull request. If not — please open an issue so we won't lose it.

Thanks!

[vladis84]

I will check

[vladis84]

An error occurs (PostgreSQL version 9.4.10)

SELECT
    ord."order"
FROM
    orders AS ord
    INNER JOIN service AS srv ON srv."order" = orders."order"
WHERE
    ord."order" = 1

dump_database_test_unique_validator.sql.zip

[samdark]

Please create another issue. It's too easy to lose the info in closed issue comments...

[sepidemahmoodi]

Hi,
what is the conclusion of this conversation?

[samdark]

It's fixed and merged to master.