Validate Unique for Subset

Validate Unique for Subset

ShaneBrennanShaneBrennan Posts: 49Questions: 18Answers: 1

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

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    edited October 2023 Answer ✓

    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;
        } ),
    
  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    Answer ✓

    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 ) { 
    
  • ShaneBrennanShaneBrennan Posts: 49Questions: 18Answers: 1

    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();

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421

    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.

Sign In or Register to comment.