Zend_Validate_Db_RecordExists against 2 fields

Question

I usualy use Zend_Validate_Db_RecordExists to update or insert a record. This works fine with one field to check against. How to do it if you have two fields to check?

 $validator = new Zend_Validate_Db_RecordExists(
        array(
            'table' => $this->_name,
            'field' => 'id_sector,day_of_week'
            )
    );

    if ($validator->isValid($fields_values['id_sector'],$fields_values['day_of_week'])){
        //true
    }

I tried it with an array and comma separated list, nothing works... Any help is welcome. Regards Andrea

Schoolbook

Answer 1

To do this you would have to extend the Zend_Validate_Db_RecordExists class.

It doesn't currently know how to check for the existence of more than one field.

You could just use two different validator instances to check the two fields separately. This is the only work around that I can see right now besides extending it.

If you choose to extend it then you'll have to find some way of passing in all the fields to the constructor ( array seems like a good choice ), and then you'll have to dig into the method that creates the sql query. In this method you'll have to loop over the array of fields that were passed in to the constructor.

Fulgurous

Yeah, the thing I'm starting to learn about Zend is that its slate is more blank than filled in. To come up with more complex features/behaviors you have to extend or write you own stuff. – Fulgurous

Answer 2

You should look into using the exclude parameter. Something like this should do what you want:

$validator = new Zend_Validate_Db_RecordExists(
array(
        'table'   => $this->_name,
        'field'   => 'id_sector',
        'exclude' => array(
            'field' => 'day_of_week',
            'value' => $fields_values['day_of_week']
        )
);

The exclude field will effectively add to the automatically generated WHERE part to create something equivalent to this:

WHERE `id_sector` = $fields_values['id_sector'] AND `day_of_week` = $fields_values['day_of_week']

Its kind of a hack in that we're using it for the opposite of what it was intended, but its working for me similar to this (I'm using it with Db_NoRecordExists).

Source: Zend_Validate_Db_NoRecordExists example

Becca

Answer 3

Sorry for the late reply.

The best option that worked for me is this:

//  create an instance of the Zend_Validate_Db_RecordExists class
//  pass in the database table name and the first field (as usual)...
$validator = new Zend_Validate_Db_RecordExists(array(
    'table' => 'tablename',
    'field' => 'first_field'
));

// reset the where clause used by Zend_Validate_Db_RecordExists
$validator->getSelect()->reset('where');

// set again the first field and the second field. 
// :value is a named parameter that will be substituted 
// by the value passed to the isValid method
$validator->getSelect()->where('first_field = ?', $first_field);
$validator->getSelect()->where('second_field = :value', $second_field);

// add your new record exist based on 2 fields validator to your element.
$element = new Zend_Form_Element_Text('element');
$element->addValidator($validator);

// add the validated element to the form.
$form->addElement($element);

I hope that will help someone :)

Although, I would strongly recommend a neater solution which would be to extend the Zend_Validate_Db_RecordExists class with the above code.

Enjoy!! Rosario

Illegalize

Answer 4

$dbAdapter = Zend_Db_Table::getDefaultAdapter();

'validators' => array('EmailAddress',  $obj= new Zend_Validate_Db_NoRecordExists(array('adapter'=>$dbAdapter,
                                                        'field'=>'email',
                                                        'table'=>'user',         
                                                        'exclude'=>array('field'=>'email','value'=>$this->_options['email'], 'field'=>'is_deleted', 'value'=>'1')
                                                      ))),

Earhart

Answer 5

For those using Zend 2, If you want to check if user with given id and email exists in table users, It is possible this way.

First, you create the select object that will be use as parameter for the Zend\Validator\Db\RecordExists object

$select = new Zend\Db\Sql\Select();
$select->from('users')
       ->where->equalTo('id', $user_id)
       ->where->equalTo('email', $email);

Now, create RecordExists object and check the existence this way

$validator = new Zend\Validator\Db\RecordExists($select);
$validator->setAdapter($dbAdapter);

if ($validator->isValid($username)) {
    echo 'This user is valid';
} else {
    //get and display errors
    $messages = $validator->getMessages();
    foreach ($messages as $message) {
        echo "$message\n";
    }
}

This sample is from ZF2 official doc

Keyek

Answer 6

You can use the 'exclude' in this parameter pass the second clause that you want to filter through.

$clause = 'table.field2 = value';
$validator = new Zend_Validate_Db_RecordExists(
  array(
    'table' => 'table',
    'field' => 'field1',
    'exclude' => $clause
  )
);

if ($validator->isValid('value') {
  true;
}

Unaware

Answer 7

I am using zend framework v.3 and validation via InputFilter(), it uses same validation rules as zend framework 2.

In my case I need to check, if location exists in db (by 'id' field) and has needed company's id ('company_id' field).

I implemented it in next way:

    $clause = new Operator('company_id', Operator::OP_EQ, $companyId);

    $inputFilter->add([
        'name' => 'location_id',
        'required' => false,
        'filters' => [
            ['name' => 'StringTrim'],
            ['name' => 'ToInt'],
        ],
        'validators' => [
            [
                'name' => 'Int',
            ],
            [
                'name' => 'dbRecordExists',
                'options' => [
                    'adapter' => $dbAdapterCore,
                    'table' => 'locations',
                    'field' => 'id',
                    'exclude' => $clause,
                    'messages'  => [
                        'noRecordFound' => "Location does not exist.",
                    ],
                ]
            ],
        ],
    ]);

In this case validation will pass, only if 'locations' table has item with columns id == $value and company_id == $companyId, like next:

select * from location where id = ? AND company_id = ?

Clammy