Uniqueness validation in form on model update/edit

Hi.

I have problem with form validation on model update/edit. For example; i wont edit user (id = 99, email = [email protected]). On "users" model, validation code looks like this:

$validator->add(['email', 'date_deleted'], new UniquenessValidator([
            'convert' => function ($data) {
                $data['email'] = $this->getDI()->get('filter')->sanitize($data['email'], 'trim');
                $data['email'] = $this->getDI()->get('filter')->sanitize($data['email'], 'email');
                $data['email'] = $this->getDI()->get('filter')->sanitize($data['email'], 'lower');
                return $data;
            },
            'message' => 'duplicate entry for ":fields" field on Users model'
                ])
        );

And execudet query from validation looks:

SELECT COUNT(*) AS `rowcount` FROM `somedb`.`users` WHERE `users`.`email` = "[email protected]" AND `users`.`date_deleted`  IS NULL AND `users`.`id` <> 99

Is ok. But i have validation in form:


$emailElement->addValidators([
            // ... PresenceOfValidator, EmailValidator...
            new UniquenessValidator([
                'model' => new UsersModel, // or $this->getEntity()
                'fields' => ['email', 'date_deleted'],
                'convert' => function ($data) {
                    $data['email'] = $this->filter->sanitize($data['email'], 'trim');
                    $data['email'] = $this->filter->sanitize($data['email'], 'email');
                    $data['email'] = $this->filter->sanitize($data['email'], 'lower');
                    return $data;
                },
                'message' => 'Entered e-mail address is currently in use by another user.'
    ]);

Executed validation from form query looks:

SELECT COUNT(*) AS `rowcount` FROM `somedb`.`users` WHERE `users`.`email` = "[email protected]" AND `users`.`date_deleted`  IS NULL

In form validation, id of entity is skipped and edited user email is not unique, because is used by self.

How to user uniqueness validator on form, to get the same result in case model-side validation?

Phalcon v3.3.2

edited May '18

If form is already associated with found User model then just remove model option and it will use your current model from form and make a proper query. I mean if it's update form just do something like this:

$user = UsersModel::findFirst($id);
$form = new UsersForm($user);


1.2k
edited May '18

Entity in form is set... Without "model" option the same result.

I can not also find in the cphalcon code, what determines and where is added for the model "id <> x".

Then just remove 'model' => new UsersModel, // or $this->getEntity() https://github.com/phalcon/cphalcon/blob/master/phalcon/validation/validator/uniqueness.zep#L293 here is this code.



1.2k

Phalcon\Validation\Exception: Model of record must be set to property "model"

How exactly you creates form? If entity is set it should be passed to validation.

Well maybe it's a bug actually. Well try to use there $this->getEntity() then



1.2k
edited May '18

Before add validation in form, getEntity() return correct model of "user".

$user = UsersModel::findFirstById($id);

$form = new UserForm($user, [
    'edit' => true
]);

// ... after post
$form->bind($this->request->getPost(), $user);

Yea that should be valid, but actually in isValid method you most likely have no arguments and that's why it happens, imho it's a bug.



1.2k
edited May '18

$this->getEntity() - still to the same.

Yes, in isValid i not have arguments, because i use "bind" method.

Using isValid with arguments, without bind method - the same results.

What you mean the same? Like exception? Or not proper validation?

You sure that getEntity returns proper model?



1.2k

The same results - executed query not contain "id <> x". Without "model" parameter i get exception.

Mhm.. Before add validations, getEntity return correct model. Dump before validations:

 Array (4) (
  [entity array] => Array (9) (
    [id] => Numeric string (1) "99"
    [email] => String (15) "[email protected]"
    [date_deleted] => NULL
  )
  [entity dirty state] => Integer (0)
  [model dirty state presistent const] => Integer (0)
  [primary key attribute from enetity] => Array (1) (
    [0] => String (2) "id"
  )
)

According to uniqueness.zep, on line 293 dirty sate conditions match true and additional conditions parameters should be added, but not work...

edited May '18

If you use isValid with correct arguments then try to remove 'model' key and see what happens.



1.2k
if ($form->isValid($this->request->getPost(), $user)) {
    // ...
}

Ok. Now, in form, uniqueness validator not require "model" parameter. Unfortunately, the executed query by uniqueness validator still does not include "id <> x".

Not sure what's going on. You can try to do some debugging by creating new validator, extending Uniqueness validator and use it, and check what you have as a record in validate method. It still might be some bug, unforunately i don't use phalcon forms at all cuz i have whole frontend written in AngularJS.



1.2k
edited May '18

I found something.

SELECT COUNT(*) AS `rowcount` FROM `somedb`.`users` WHERE `users`.`email` = '[email protected]' AND `users`.`id` <> '99'

The table with users contained 2 records with the same email address. One of the records contained "date_deleted".

In the latter case, the executed query contained "id <> x". However, it did not check all of the fields listed (email, date_deleted). It checked only the "email" field, which is why it returned the result as false. The validator excludes the current model (by id) when checking. But it does not check the fields that I gave in the options.

Wee. I have a solution for my case. In form - uniqueness validation, validator not see "fields" option. Validator search records only by field name. We must use "except" option, like this:

new UniquenessValidator([
                'except' => ['date_deleted' => $this->getEntity()->date_deleted],
                'convert' => function ($data) {
                    $data['email'] = $this->filter->sanitize($data['email'], 'trim');
                    $data['email'] = $this->filter->sanitize($data['email'], 'email');
                    $data['email'] = $this->filter->sanitize($data['email'], 'lower');
                    return $data;
                },
                'message' => 'msg on fail '
]),

Thanks for help.

UniquenessValidator actually doesn't accept fields field, like it adds validator to element or uses fields from add method from $validation->add(), but it doesn't contain itself information about fields. But yea, your solution is fine i guess, still i guess there is a bug and i think when we have entity used like new Form($entity) it should be passed to validation even if we use isValid() or bind() without entity.



1.2k

Aww.. However, this is not what I meant. I can only exclude certain data, but I can not exclude data that is "NOT NULL". The "except" option only supports exclude the value via "field <> x" or "field NOT IN (x ...)", whten "x" can not equal "NULL";

At the moment, I can only use callback validation.

Oh you mean it should exclude also where date_deleted is NOT NULL? Yea i guess it's obvious, well i think there was somewhere NFR for it wasn't implemented. I guess it's a time to add it.

Db Uniqueness validator from incubator supporte exclude option to this cases

edited May '18

WHERE %s = ? AND %s != doesn't look like it supports IS NOT NULL/IS NULL.

well, we have to improve it ;)

Yea but just improve validator in core already. https://github.com/phalcon/cphalcon/issues/12763 there was an issue, i guess maybe we should reopen i and implement it.