php - CakePHP 3 Multiple isUnique allowing NULL duplicate

172

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 isNULL, 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 onNULL values?

Thanks in advance for your help.

680

Answer

Solution:

Comparing againstNULL using ordinary comparison operators, iecolumn = NULL (which the unique rule does), should always beNULL (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 aNULL 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 .

Here's a basic example for an overridenIsUnique rule class, it basically just adds aIS operator to the condition key, so thatNULL checks end up ascolumn 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 overridenIsUnique::_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

199

Answer

Solution:

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.

People are also looking for solutions to the problem: php - Can not authorize in Active Directory in Symfony 3

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.