DbNoRecordExists with exclude option not working as expected

[spectravp]

I have two input filter specifications defined; default one and one for POST. DbNoRecordExists works as expected on POST requests with no "exclude" option set. That is, the api returns a 422 Unprocessable Entity with the correct validation messages when trying to insert/create/POST duplicate data.

However, when the exclude option is set for update/PATCH, the validator is returning true (or is Valid) even though a record exists with the value. The only thing saving the integrity of the data is that the api catches an exception when the DB adapter throws an exception due to duplicate data on a field with a UNIQUE index.

Config and link to video showing POST and PATCH requests as well as config and resource code are below.

'Tattle\\V1\\Rest\\MerchantTypes\\Controller' => array(
            'input_filter' => 'Tattle\\V1\\Rest\\MerchantTypes\\Validator',
            'POST' => 'Tattle\\V1\\Rest\\MerchantTypes\\ValidatorPOST',
        ),

'Tattle\\V1\\Rest\\MerchantTypes\\Validator' => array(
            0 => array(
                'name' => 'id',
                'required' => false,
                'allow_empty' => true,
                'continue_on_empty' => true,
                'filters' => array(
                    0 => array(
                        'name' => 'Zend\\Filter\\Digits',
                        'options' => array(),
                    ),
                ),
                'validators' => array(
                    0 => array(
                        'name' => 'Zend\\Validator\\Digits',
                        'options' => array(),
                    ),
                ),
                'description' => 'Primary key for the merchant type',
                'continue_if_empty' => true,
            ),
            1 => array(
                'name' => 'merchant_type',
                'required' => true,
                'filters' => array(
                    0 => array(
                        'name' => 'Zend\\Filter\\StringTrim',
                        'options' => array(),
                    ),
                    1 => array(
                        'name' => 'Zend\\Filter\\StripTags',
                        'options' => array(),
                    ),
                    2 => array(
                        'name' => 'Zend\\Filter\\StripNewlines',
                        'options' => array(),
                    ),
                ),
                'validators' => array(
                    0 => array(
                        'name' => 'ZF\\ContentValidation\\Validator\\DbNoRecordExists',
                        'options' => array(
                            'table' => 'merchant_types',
                            'field' => 'merchant_type',
                            'adapter' => 'TattleDbAdapter',
                            'message' => 'A merchant type by this name already exists',
                            'exclude' => 'id = ?',
                        ),
                    ),
                ),
                'description' => 'Merchant type description (Restaurant, Auto Body Shop, Deli, etc)',
                'allow_empty' => false,
                'continue_if_empty' => false,
            ),
        ),
        'Tattle\\V1\\Rest\\MerchantTypes\\ValidatorPOST' => array(
            0 => array(
                'name' => 'merchant_type',
                'required' => true,
                'filters' => array(
                    0 => array(
                        'name' => 'Zend\\Filter\\StringTrim',
                        'options' => array(),
                    ),
                    1 => array(
                        'name' => 'Zend\\Filter\\StripTags',
                        'options' => array(),
                    ),
                    2 => array(
                        'name' => 'Zend\\Filter\\StripNewlines',
                        'options' => array(),
                    ),
                ),
                'validators' => array(
                    0 => array(
                        'name' => 'ZF\\ContentValidation\\Validator\\DbNoRecordExists',
                        'options' => array(
                            'table' => 'merchant_types',
                            'field' => 'merchant_type',
                            'schema' => 'tattle_v1',
                            'adapter' => 'TattleDbAdapter',
                            'message' => 'A merchant type by this name already exists',
                        ),
                    ),
                ),
                'description' => 'Merchant type description (Restaurant, Auto Body Shop, Deli, etc)',
                'allow_empty' => false,
                'continue_if_empty' => false,
            ),
        ),
    ),

http://spectravp.com/dbnorecord.mov

[weierophinney]

I think your exclude definition is at fault here.

DbNoRecordExists does the following:

• Creates a SELECT statement, using the configured schema and table
• Creates a WHERE clause, using the configured field name: WHERE <field> != ?
• Passes the $value as a parameter when executing the SELECT statement.

When you add an exclude clause, it MUST be a literal OR also contain the parameter value to use in the statement. What you're doing is specifying WHERE (<field> != ?) AND (id = ?) - but because you're not specifying a value for id when creating the instance, it's getting a null -- which, because the clauses are AND'd together, means nothing will ever match.

My guess is you're anticipating that id will be populated from the URI and/or other fields. Currently, the RecordExists and DbNoRecordExists do not accept any $context, so they are unable to pull that data; additionally, we never pass URI parameters anyways, only those from the message body.

You have a few options.

First, you can extend the validator to have it inject the route match parameters and/or ID via the factory.

public function factory($validators)
{
    // You likely want to mimic the logic in the ZF\ContentValidation validator factories here, btw, as
    // they pull the database adapter from the service locator before passing options to the validator.
    $validator = new MyCustomDbNoRecordExists($options);
    $services = $validators->getServiceLocator();
    $event     = $services->get('MvcEvent');
    $validator->setRouteMatch($event->getRouteMatch());
    // or
    $validator->setId($event->getRouteMatch()->getParam('name_of_your_route_identifier');

    return $validator;
}

Another option is to register a custom listener to the route event, and have it run after the default ContentValidation listener (i.e., -651 or lower). That could look like this:

public function onRoute($e)
{
    $routeMatch = $e->getRouteMatch();
    if ($routeMatch->getParam('controller') !== 'Your\Controller\Service') {
        return;
    }
    if (! $routeMatch->getParam('your_route_identifier_name')) {
        return;
    }

    $request = $e->getRequest();
    if (! $request->isPatch()) {
        return;
    }
    $app = $e->getTarget();
    $services = $app->getServiceManager();
    $validators = $services->get('ValidatorPluginManager');
    $validator = $validators->get('ZF\\ContentValidation\\Validator\\DbNoRecordExists');
    $validator->setExclude(array('field' => 'id', 'value' => $routeMatch->getParam('your_route_identifier_name'));

    $params = $e->getParam('ZFContentNegotiationParameterData');
    if ($validator->isValid($params['merchant_types'])) {
        return; // all is well
    }
    // Raise an error!
    /* ... */
}

Finally, of course, you could do the comparison in your REST resource or RPC controller prior to performing the update, and raise the appropriate error from there.