Zend_Validate_Db_RecordExists exclude logic issue

Question

I've got a form for a job application, where administrator needs to pick from the list of certain user ids. The list contains only user ids of type "employer", however I want to validate the administrator input, so if he manually inserts an id that doesn't exist or is for user of different type than "employer", the validation should fail. I thought that code to do this would be:

new Zend_Validate_Db_RecordExists(
    array(
        'table' => 'users',
        'field' => 'id',
        'exclude' => "mode != 'employer'"
    )
)

so, I'm searching for all the records in table users, excluding those where mode != 'employer' - if such record exists, where id is equal to the one picked from input, it passes the validation. However, the code above doesn't work - I have to do 'exclude' => "mode = 'employer'", so exclude actually equals where statement. My understanding of the logic here is wrong - can somebody tell me why?

PHP: 5.2.17, Zend: 1.10.4

EDIT: (the comment to @ro ko enquiries, as it probably clears things out)

Please find the table and sample code here: http://pastebin.com/C7AXMNTZ . In my understanding this should return valid for Joker (is employer), but false for Kingpin (not employer) and Poison Ivy (not in the db) - as you can see the results are not what I'm expecting.

Gerard Roche · Answer 1 · 2016-09-28T09:47:53.383

A) 'exclude' => "mode != 'employer'"

    $id = new Zend_Form_Element_Select("id");
    $id->setRegisterInArrayValidator(false);
    $id->addValidator(new Zend_Validate_Db_RecordExists(array(
        'table' => 'villains',
        'field' => 'id',
        'exclude' => "mode != 'employer'"
    )));

Produces the following query:

SELECT `villains`.`id` 
    FROM `villains` 
    WHERE (`id` = :value) AND (mode != 'employer') 
    LIMIT 1

B) 'exclude' => "mode = 'employer'"

    $id = new Zend_Form_Element_Select("id");
    $id->setRegisterInArrayValidator(false);
    $id->addValidator(new Zend_Validate_Db_RecordExists(array(
        'table' => 'villains',
        'field' => 'id',
        'exclude' => "mode = 'employer'"
    )));

Produces the following query:

SELECT `villains`.`id` 
    FROM `villains` 
    WHERE (`id` = :value) AND (mode = 'employer') 
    LIMIT 1

C) 'exclude' => array("field" => "mode", "value" => "employer")

    $id = new Zend_Form_Element_Select("id");
    $id->setRegisterInArrayValidator(false);
    $id->addValidator(new Zend_Validate_Db_RecordExists(array(
        'table' => 'villains',
        'field' => 'id',
        'exclude' => array(
            "field" => "mode",
            "value" => "employer"
        )
    )));

Produces the following query:

SELECT `villains`.`id` 
    FROM `villains` 
    WHERE (`id` = :value) AND (`mode` != 'employer') 
    LIMIT 1

Outcomes

You want B. It is confusing and arguably the logic and behaviour of the component is backwards. Nonetheless, the behaviour you want is from example B.

Appendix

We can hack a test (and I really mean hack together) to check that the above works as expected.

Both test1 and test2 pass, but as you can see from the providers, they both produce different results.

class SO14706653Test extends PHPUnit_Framework_TestCase
{
    /**
     * @var Zend_Test_PHPUnit_Db_Connection
     */
    public $dbConnection;

    public function getRowCount($tableName) {
        $query = "SELECT COUNT(*) FROM ".$this->dbConnection->quoteSchemaObject($tableName);
        return (int) $this->dbConnection->getConnection()->query($query)->fetchColumn();
    }

    // hack a very quick setup for tests
    public function setup() {
        $app = new Zend_Application(APPLICATION_ENV, APPLICATION_PATH . '/configs/application.ini');
        $app->bootstrap();
        $dbAdapter = $app->getBootstrap()->getResource('db'); /* @var $db Zend_Db_Adapter_Pdo_Mysql */
        $this->dbConnection = new Zend_Test_PHPUnit_Db_Connection($dbAdapter, 'unittests');

        $dbAdapter->exec("CREATE TABLE IF NOT EXISTS `villains` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `name` varchar(255) NOT NULL,
              `mode` varchar(255) NOT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB  DEFAULT CHARSET=latin1");

        $dbAdapter->exec('DELETE FROM villains'); // clean out db data

        $dbAdapter->exec("
            INSERT INTO `villains` VALUES(1, 'Joker', 'employer');
            INSERT INTO `villains` VALUES(2, 'Kingpin', '');
            INSERT INTO `villains` VALUES(3, 'Penguin', '');
        ");
    }

    // ensure the above setup is working as expected
    public function assertPreConditions() {
        $this->assertEquals(3, $this->getRowCount('villains')); 
    }

    public function provideTest1()
    {
        return [
            // form data        is valid?       isRequired?
            [['id' => '1'],     false,          false],
            [['id' => '2'],     true,           false],
            [['id' => '3'],     true,           false],
            [['id' => ''],      true,           false],
            [[],                true,           false],
            [['id' => '856'],   false,          false],
            [['id' => '856'],   false,          true],
            [['id' => ''],      false,          true],
            [[],                false,          true],
        ];
    }

    public function provideTest2()
    {
        return [
            //  form data       is valid?       isRequired?
            [['id' => '1'],     true,           false],
            [['id' => '2'],     false,          false],
            [['id' => '3'],     false,          false],
            [['id' => ''],      true,           false],
            [[],                true,           false],
            [['id' => '856'],   false,          false],
            [['id' => '856'],   false,          true],
            [['id' => ''],      false,          true],
            [[],                false,          true],
        ];
    }

    /**
     * @dataProvider provideTest1
     */
    public function test1(array $data, $isValid, $isRequired)
    {
        $form = new Zend_Form();
        $id = new Zend_Form_Element_Select("id");
        $id->setRegisterInArrayValidator(false);
        $id->addValidator(new Zend_Validate_Db_RecordExists(array(
            'table' => 'villains',
            'field' => 'id',
            'exclude' => "mode != 'employer'"
        )));
        $id->setRequired($isRequired);
        $form->addElement($id);

        // produces the query
        // SELECT `villains`.`id`
        // FROM `villains`
        // WHERE (`id` = :value) AND (mode != 'employer')
        // LIMIT 1

        $this->assertSame($isValid, $form->isValid($data));
    }

    /**
     * @dataProvider provideTest2
     */
    public function test2(array $data, $isValid, $isRequired)
    {
        $form = new Zend_Form();
        $id = new Zend_Form_Element_Select("id");
        $id->setRegisterInArrayValidator(false);
        $id->addValidator(new Zend_Validate_Db_RecordExists(array(
            'table' => 'villains',
            'field' => 'id',
            'exclude' => "mode = 'employer'"
        )));
        $id->setRequired($isRequired);
        $form->addElement($id);

        // produces the query
        // SELECT `villains`.`id` 
        // FROM `villains` 
        // WHERE (`id` = :value) AND (mode = 'employer') 
        // LIMIT 1

        $this->assertSame($isValid, $form->isValid($data));
    }
}

score 0 · Answer 2 · edited May 23 '17 at 12:00

0

You pass an array in the exclude option. Eg.

$this->getElement('username')->addValidator(
    new Zend_Validate_Db_NoRecordExists(
        array(
        'table' => 'user', 
        'field' => 'username', 
        'exclude' => array(
                'field' => 'username', 
                'value' => $username
            )
        )
    )
);

Check here for more info.

Edit: After reading the question again, I see I didn't actually answer it. The only other advice I would give is using the quoteInto statement as in the documentation.

$email     = 'user@example.com';
$clause    = $db->quoteInto('email != ?', $email);
$validator = new Zend_Validate_Db_RecordExists(
    array(
        'table'   => 'users',
        'field'   => 'username',
        'exclude' => $clause
    )
);

Also here is a link to another question about Zend_Validate_Db_RecordExistswhich has a very good answer.

How to modify zend Db_RecordExists validator where clause?

edited May 23 '17 at 12:00

Community

1
1

answered Feb 05 '13 at 13:08

A. J. Parr

7,731
2
31
46

1

Excuse me, but how does that answer my question? – eithed Feb 05 '13 at 13:14
I mean - I can use a clause, as per `You can also provide a string to the exclude clause so you can use an operator other than !=` – eithed Feb 05 '13 at 13:21
no change. If I'm not mistaken, the use of quoteInto shouldn't reduce any different results, as it's to escape any quotes (was worth a try though, maybe it's a bug that's affecting strings and not objects - ie. PDO statement, dunno) – eithed Feb 06 '13 at 09:35

ro ko · Answer 3 · 2013-02-05T13:36:10.737

0

I think you got confused here. Logic is fine with the validator.

How?
Well exclude here adds negation to your condition.

'exclude' => "mode = 'employer'"

OR

'exclude' => array(
                'field' => 'mode', 
                'value' => 'employer'
            )

is interpreted as validator should stand valid for other than mode = employer condition.

EDIT: If you see In the manual you'll see a description: "The above example will check the table to ensure no records other than the one where id = $user_id contains the value $username." this statement should clear things for you I assume.

edited Feb 05 '13 at 13:36

answered Feb 05 '13 at 13:24

ro ko

2,906
3
37
58

But I want it to stand valid only for the rows, where mode = 'employer' (so, exclude all the rows where mode != 'employer'). Yes, I'm confused :) – eithed Feb 05 '13 at 13:44
Additionally - `exclude: Sets records which will be excluded from the search.` - thus my initial `mode != 'employer'` should work, as I don't want these rows to be taken into consideration. Please, don't tell me it's a bug with my version of Zend... – eithed Feb 05 '13 at 13:57
@eithed could you add actual data set and you're expected result set and I'll try to make an explanation on it + others might also be able to make it more clear perhaps. – ro ko Feb 06 '13 at 02:30
1

I am having a feeling that you are comparing the logic for Db_NoRecordExists in your case. – ro ko Feb 06 '13 at 02:33
please find the table and sample code here: http://pastebin.com/C7AXMNTZ . In my understanding this should return valid for Joker (is employer), but false for Kingpin (not employer) and Poison Ivy (not in the db) - as you can see the results are not what I'm expecting. Well, I wasn't mentioning anything about NoRecordExists :D my question is purely about this validator, though exclude option is the same for both validators. – eithed Feb 06 '13 at 09:29

eithed · Accepted Answer · 2013-02-11T16:45:11.933

0

As per documentation (search for The above example will check the table to ensure no records other than the one where id = $user_id contains the value $username.) the exclude option indeed works as WHERE statement.

I can only say that whoever though that using such keyword for this functionality is a good idea had a wicked sense of humor.

edited Feb 11 '13 at 16:45

answered Feb 07 '13 at 14:09

eithed

3,933
6
40
60

Zend_Validate_Db_RecordExists exclude logic issue

4 Answers4

Outcomes

Appendix