CakePHP 3 Multiple isUnique allowing NULL duplicate

Question

I have the following rule in my BlockedTable.php

public function buildRules(RulesChecker $rules)
{
    $rules->add($rules->isUnique(['date', 'time']),
        ['message' => 'unique error']);

    return $rules;
}

Up until now this has worked fine - if I try to save a new record with an already existing date and time, it prevents me from saving.

However, if my time is NULL, such as the below entry;

╔════╦══════════════╦═══════╗
║ ID ║  Date        ║ Time  ║
╠════╬══════════════╬═══════╣
║  1 ║ 22/08/1985   ║ NULL  ║
╚════╩══════════════╩═══════╝

The rule still allows me to save a new entry with the same data. So if I try to save $date = 22/08/1985 and $time = NULL it saves fine and there is a duplicate record in my database. I would of expected it to have failed due to the above rule?

Why is this happening? And how can I prevent duplicate entries on NULL values?

Thanks in advance for your help.

Answer 1

Comparing against NULL using ordinary comparison operators, ie column = NULL (which the unique rule does), should always be NULL (AFAIK this is at least the case in MySQL and Postgres), thus nothing is being found, and consequently the unique check will pass whenever there is a NULL value involved.

If you want to prevent this behavior, you'll have to use a custom rule, as the built-in one simply doesn't support it. I think this is something worth an enhancement, so you might want to open a ticket over at GitHub.

Here's a basic example for an overriden IsUnique rule class, it basically just adds a IS operator to the condition key, so that NULL checks end up as column IS NULL.

public function __invoke(EntityInterface $entity, array $options)
{
    if (!$entity->extract($this->_fields, true)) {
        return true;
    }

    $alias = $options['repository']->alias();
    $conditions = $this->_alias($alias, $entity->extract($this->_fields));
    if ($entity->isNew() === false) {
        $keys = (array)$options['repository']->primaryKey();
        $keys = $this->_alias($alias, $entity->extract($keys));
        if (array_filter($keys, 'strlen')) {
            $conditions['NOT'] = $keys;
        }
    }

    // handle null values
    foreach ($conditions as $key => $value) {
        if ($value === null) {
            $conditions[$key . ' IS'] = $value;
            unset($conditions[$key]);
        }
    }

    return !$options['repository']->exists($conditions);
}

Theoretically you could do it in an overriden IsUnique::_alias() method too, which would work without having to reimplement code from the original rule class, it's not really the right place though.

https://github.com/cakephp/cakephp/blob/3.2.5/src/ORM/Rule/IsUnique.php

See also

• Cookbook > Validation > Applying Application Rules > Creating Custom Rule objects

Answer 2

I know this is an old post but, since I had to search a lot to solve this problem, I think i should post here how I did it.

In cakePHP 3.6 you can solve this problem by changing property allowMultipleNulls to false in class isUnique.

Regards.