CODEWITHSUNDEEP
phpyii2
kasoft
kasoft

Reputation: 466

yii2 unique validator only when field not empty

In Yii2 I have two fields in my Database: email and shopId

These are my rules in the Model:

[['email'],'default','value' => NULL],
[['shopId'], 'integer'],
[['email','shopId'], 'unique', 'targetAttribute' => ['email', 'shopId'], 'message' => 'Already taken!'],

This is not working when I have two entries with e.g. email="NULL" and shopId="1".

How can I solve this?

Upvotes: 8

Views: 7279

Answers (4)

ajmedway
ajmedway

Reputation: 1492

I have created a solution to enforce uniqueness across a set of fields, including nullable fields, which utilises the yii\validators\UniqueValidator::$filter callback in a generic PHP Trait that can be simply dropped into any Yii2 model. Full code examples below:

Generic Trait [common/models/traits/UniqueValidatorFilterTrait.php]

<?php

namespace common\models\traits;

use yii\db\ActiveQuery;

/**
 * Trait UniqueValidatorFilterTrait provides custom anonymous function callback methods.
 *
 * @package common\models\traits
 * @author Alec Pritchard <[email protected]>
 */
trait UniqueValidatorFilterTrait
{
    /**
     * Custom anonymous function for [[yii\validators\UniqueValidator::$filter]], used to modify the passed-in
     * [[\yii\db\ActiveRecord]] query to be applied to the DB query used to check the uniqueness of the input value.
     *
     * @param ActiveQuery $query
     * @param array $nullableUniqueFields the field names to ensure are considered and validated for the uniqueness
     * of a set of model attributes, even if some have empty/null values.
     * @see \yii\validators\UniqueValidator::$filter
     */
    public function filterFunctionUniqueWithNull(ActiveQuery $query, array $nullableUniqueFields = [])
    {
        // check if at least one of the $nullableUniqueFields currently has an empty value loaded
        $hasEmptyUniqueField = false;
        foreach ($nullableUniqueFields as $field) {
            if (empty($this->{$field})) {
                $hasEmptyUniqueField = true;
                break;
            }
        }
        // proceed to modify AR query
        if ($hasEmptyUniqueField) {
            // change query item for the $nullableUniqueFields, which only checks against empty string by design
            // @link https://github.com/yiisoft/yii2/issues/4333#issuecomment-57739619
            // ensure the composite unique constraint is applied to include NULL values for all $nullableUniqueFields
            foreach ($query->where as $whereItemKey => $whereItem) {
                if (!is_array($whereItem)) continue;
                foreach ($whereItem as $columnName => $value) {
                    // check if this column is one of the unique fields and if it currently has an empty string
                    if (str_replace($nullableUniqueFields, '', $columnName) != $columnName
                        && $value === '') {
                        // change from '' to NULL
                        $query->where[$whereItemKey][$columnName] = null;
                    }
                }
            }
        }
    }
}

Example Model Usage

<?php

namespace common\models;

use Yii;
use common\models\traits\UniqueValidatorFilterTrait;

/**
 * This is the model class for table "my_table".
 *
 * @property int $id
 * @property null|string $email
 * @property int $shop_id
 */
class MyTable extends \yii\db\ActiveRecord
{
    use UniqueValidatorFilterTrait;

    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'my_table';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [
                ['email', 'shop_id'],
                'unique',
                'targetAttribute' => ['email', 'shop_id'],
                // ensure the composite unique constraint is applied to include NULL values for specified nullable fields
                'filter' => function (MyTableQuery $query) {
                    /* @see \common\models\traits\UniqueValidatorFilterTrait::filterFunctionUniqueWithNull() */
                    $this->filterFunctionUniqueWithNull($query, ['email', 'shop_id']);
                },
                // ensure that we don't skip on empty values, namely the `email` and/or `shop_id`
                'skipOnEmpty' => false,
            ],
        ];
    }

See the full discussion about this shortcoming of the Yii2 core unique validator, and reasons given by core dev team here: https://github.com/yiisoft/yii2/issues/4333

Upvotes: 0

kasoft
kasoft

Reputation: 466

I used the when condition inside the rule

[
    ['email', 'shopId'],
    'unique',
    'targetAttribute' => ['email', 'shopId'],
    'message' => 'Diese E-Mail Adresse ist bereits registriert!',
    'when' => function ($model) {
        return !empty($model->email);
    }
],

Upvotes: 4

MrBlc
MrBlc

Reputation: 43

While your solution is working it isn't technically correct. Validation rule 

[['email','shopId'], 'unique', 'targetAttribute' => ['email', 'shopId']]

will validate email to be unique with given shopId if email is not empty (desired functionality), but it will also validate shopId to be unique with given email if shopId is not empty (unwanted). You are validating two fields with two queries to DB.

Validation rule that fits your needs is

[['email'], 'unique', 'targetAttribute' => ['email', 'shopId']]

saying "If email is not empty, check if combination of email and shopId is unique and bind result to email attribute".

Upvotes: 3

Sergei Kasatkin
Sergei Kasatkin

Reputation: 385

Set skipOnEmpty property to false http://www.yiiframework.com/doc-2.0/yii-validators-validator.html#$skipOnEmpty-detail

Upvotes: 2

Related Questions