Validate Unique for Subset

ShaneBrennan

Hi Everyone

Hope someone can help. I'm using Editor to display a list of "Staff Roles" for a specific subscriber, which works great:

Editor::inst( $db, 'tblStaffRoles' )
->fields(
Field::inst( 'Details' )
->validator( 'Validate::notEmpty', array(
'message' => 'This is a required field.') )
->validator( 'Validate::unique', array(
'message' => 'This Staff Role already exists, please try again.'))
)
->where('subscriberID',$subscriberID)
->process( $_POST )
->json();

But say I'm trying to add the Role "Auditor" to subscriberID 1 it works fine, except if subset subscriberID 2 contains "Auditor" - the above validation won't allow the record to be saved. Is there away of getting the Validator just to check subscriberID 1? Thank you inadvance for any help

rf1234

You could use a custom validator and check whether the value already exists for the respective subscriberID.

Alternatively you could place a unique index on "Details" and "subscriberID" in your database. Whenever the insertion of a duplicate occurs an SQL error (1062 Duplicate entry) will be produced that you could catch at the front end and overwrite with something users understand.

I use the second way frequently because it is less code to write:

editor
    .on( 'postSubmit', function ( e, json, data, action ) {
        if (json.error) {
            if ( json.error.indexOf('1062 Duplicate entry') >= 0 ) {
                json.error = "This Staff Role already exists, please try again."; 
            }
        }
    })

In your PHP script you could do this alternatively (using Editor's db handler):

Field::inst( 'Details' )
    ->validator( function ( $val, $data, $opts ) use ( $db ) {
        $result = $db->raw()
            ->bind( ':subscriberID',  $subscriberID )
            ->bind( ':Details',       $val )    
            ->exec( 'SELECT COUNT(*) AS valueCount 
                       FROM tblStaffRoles
                      WHERE subscriberID      = :subscriberID
                        AND TRIM(`Details`)   = :Details' );
        $row = $result->fetch(PDO::FETCH_ASSOC);
        if ( (bool)$row["valueCount"] ) {
            return "This Staff Role already exists, please try again.";
        }
        return true;
    } ),

rf1234

You might need to change the second line of code in your PHP like this to be able to use $subscriberID in your custom validator.

->validator( function ( $val, $data, $opts ) use ( $db, $subscriberID ) { 

ShaneBrennan

Thank you rf1234, I had to take the trim out of the exec string, but apart from that it worked first time.

Editor::inst( $db, 'tblStaffRoles' )
->fields(
Field::inst( 'Details' )
->validator( function ( $val, $data, $opts ) use ( $db, $subscriberID ) {
$result = $db->raw()
->bind( ':subscriberID ', $subscriberID )
->bind( ':Details', trim($val) )
->exec( 'SELECT COUNT(*) AS valueCount
FROM tblStaffRoles
WHERE subscriberID = :subscriberID
AND Details = :Details' );
$row = $result->fetch(PDO::FETCH_ASSOC);
if ( (bool)$row["valueCount"] ) {
return "This Staff Role already exists, please try again.";
}
return true;
} ),
)
->where('HBIsubscriberID',$HBIsubscriberID)
->process( $_POST )
->json();

rf1234

Sounds good! Great you got it working. Please use Markdown (see below) to make your code more legible. I mean next time.

Those built-in validators etc. are quite nice but for more complex use cases you will need to roll your own.