Unique validation rule needs to check if target attribute has previous validation errors

[xcopy]

What steps will reproduce the problem?

1. Create a claim table

$this->createTable('{{%claim}}', [
    'id' => $this->primaryKey(),
    'event_date' => $this->date()->notNull(),
]);

2. Create a medication table

$this->createTable('{{%medication}}', [
    'id' => $this->primaryKey(),
    'name' => $this->string()->notNull()->unique(),
]);

3. Create a junction table claim_medication

$this->createTable('{{%claim_medication}}', [
    'id' => $this->primaryKey(),
    'claim_id' => $this->integer()->notNull(),
    'medication_id' => $this->integer()->notNull(),
]);

$this->addForeignKey(
    'fk-claim_medication-claim_id',
    'claim_medication',
    'claim_id',
    'claim',
    'id'
);

$this->addForeignKey(
    'fk-claim_medication-medication_id',
    'claim_medication',
    'medication_id',
    'medication',
    'id'
);

// IMPORTANT: unique index on two columns
$this->createIndex(
    'idx-claim_medication-unique',
    'claim_medication',
    ['claim_id', 'medication_id'],
    true
);

Generated rules in the ClaimMedication model:

public function rules()
{
    return [
        [['claim_id', 'medication_id'], 'required'],
        [['claim_id', 'medication_id'], 'default', 'value' => null],
        [['claim_id', 'medication_id'], 'integer'],
        [['strength', 'quantity', 'duration'], 'string', 'max' => 255],
        [['claim_id', 'medication_id'], 'unique', 'targetAttribute' => ['claim_id', 'medication_id']],
        [['claim_id'], 'exist', 'skipOnError' => true, 'targetClass' => Claim::className(), 'targetAttribute' => ['claim_id' => 'id']],
        [['medication_id'], 'exist', 'skipOnError' => true, 'targetClass' => Medication::className(), 'targetAttribute' => ['medication_id' => 'id']],
    ];
}

4. Try to create a new ClaimMedication model

$model = new app\models\ClaimMedication();

// case 1
$model->claim_id = 1;
$model->medication_id = null;
$model->save(); // all good, just one error: "Medication ID cannot be blank."

// case 2
$model->claim_id = 1;
$model->medication_id = 1;
$model->save(); // all good, no errors

// case 3
$model->claim_id = 1;
$model->medication_id = ''; // an empty string
$model->save(); // not good

What is the expected result?

Still expecting a single error on case 3: Medication ID cannot be blank.

What do you get instead?

Database exception on case 3 (i.e. unique rule in action):

yii\db\Exception with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: ""
The SQL being executed was: SELECT EXISTS(SELECT * FROM "claim_medication" WHERE ("claim_medication"."claim_id"=1) AND ("claim_medication"."medication_id"=''))'

Additional info

Q	A
Yii version	2.0.46-dev
PHP version	7.4.29
Operating system	Ubuntu 20.04

IMPORTANT: If I swap the order of the rules required & default, then everything works as expected. But this change may affect of other models. The problem occurs precisely when there is more than one attribute in the rule unique.

[bizley]

There is nothing wrong with the rules themselves. You need to modify the integer rule config though:

[['claim_id', 'medication_id'], 'integer', 'skipOnEmpty' => false],

Without that addition empty strings will not be validated and you will pass not allowed value to your DB.

[xcopy]

There is nothing wrong with the rules themselves. You need to modify the integer rule config though:

[['claim_id', 'medication_id'], 'integer', 'skipOnEmpty' => false],

Without that addition empty strings will not be validated and you will pass not allowed value to your DB.

It didn't help. Still can see that exception.

Yes, there is nothing wrong with the rules themselves, I agree. But, can anyone has/had the same issue? Any thoughts?

[bizley]

Hmm, I can see a slight problem indeed here, we need to correct this.

When unique rule is about to be fired and it uses one of the target attributes that has some error already, it should not be fired when skipOnError is set to true (default).

[xcopy]

...one of the target attributes that has some error already

@bizley yeah, that's what I wanted to say. Thanks.

[WinterSilence]

hm...I'm check this case with MySQL8 and SELECT EXISTS(SELECT * FROM order_item WHERE (order_item.order_id=1) AND (order_item.item_id='')) executed without errors. What's database(and version) you use?

[xcopy]

What's database(and version) you use?

PostgreSQL 12.x