Node.js datatables editor - Custom/Unique validation for multiple columns

Capamania

The Validator.dbUnique() let's me validate a value prior to insert if the value is unique in that column. Ref. https://editor.datatables.net/manual/nodejs/validation

new Field( 'groups.name' )
new Field( 'groups.product_id' ),
    .validator( Validator.dbUnique() )
new Field( 'groups.type' )
new Field( 'groups.status' );       

What I need though is a validation for multiple columns. So insert the new record only if the product_id IS NOT of type=1 and status=2 found.

Here is a php example combining multiple columns https://datatables.net/forums/discussion/57729/unique-validation-for-multiple-columns

->validator( function($editor, $action, $data){
    if ($action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT){
        foreach ($data['data'] as $pkey => $values ){
            $count = $editor->db()->query('select')->get('*')->table('teachers_subjects')
                ->where('teachers_subjects.teacher', $values['teachers_subjects']['teacher'])
                ->where('teachers_subjects.subject', $values['teachers_subjects']['subject'])
                ->exec()
                ->count();                      
            if ($count == 1){
                return 'This selection is already in the database.';
            }
        }
    }
})

But how to do so with node.js ?

Rf. https://editor.datatables.net/manual/nodejs/validation#Global-validators

allan

.validator( async (editor, action, data) => {
  if (action === 'create' || action === 'edit') {
    for (let [key, value] of Object.entries(data.data)) {
      let count = await editor
        .db()
        .count('*')
        .from('teachers_subjects')
        .where('teachers_subjects.teacher', values.teachers_subjects.teacher)
        .where(...);

      if (count > 1) {
        return 'Already in the db';
      }
    }
  }
})

The Editor NodeJS libraries use KnexJS for the database interaction and the KnexJS documentation is available on its site.

I have to warn you - I haven't tried running the above code - I've just translated the PHP into modern node (so you might need to polyfill Object.entries depending on what version you are using) and I might have made a daft mistake somewhere - apologies if so (check the count variable - it might be an array...), but hopefully that will get you started with it.

Allan

Capamania

Thanks Allan! That's how I got it to work:

.validator( async (editor, action, data) => {
  if (action === 'create') {
    for (let [key, value] of Object.entries(data.data)) {
      let check = await editor
        .db()
        .from('groups')
        .where('groups.product_id', value.groups.product_id)
        .where('groups.type', value.groups.type)
        .where('groups.status', value.groups.status);

      if (check) {
        return 'Record already exists!';
      }
    }
  }
})

Object.entries() not being supported by IE as per https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/entries#Polyfill might be an issue if user use IE I suppose. How would the example look like with the suggested polyfill ?

if (!Object.entries) {
  Object.entries = function( obj ){
    var ownProps = Object.keys( obj ),
        i = ownProps.length,
        resArray = new Array(i); // preallocate the Array
    while (i--)
      resArray[i] = [ownProps[i], obj[ownProps[i]]];
    
    return resArray;
  };
}

allan

Keep in mind that the validator is run on the server-side in NodeJS, so that code is never going to be run by IE (thankfully! The use of a fat arrow function, async, let and desctructuring would all cause it problems!).

Allan

allan

How would the example look like with the suggested polyfill ?

Missed this question. The polyfill adds the missing function, so it would look exactly the same.

Allan

Capamania

Oh ... I totally didn't think of that. Then it's all good.

Also a minor change to my solutions ... it returns an array so I needed to check on:

      if (check.length > 0) {
        return 'Record already exists!';
      }

Many thanks!