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

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

CapamaniaCapamania Posts: 229Questions: 79Answers: 5

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,449Questions: 1Answers: 10,055 Site admin
    Answer ✓
    .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

  • CapamaniaCapamania Posts: 229Questions: 79Answers: 5
    edited February 2020

    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;
      };
    }
    
  • allanallan Posts: 61,449Questions: 1Answers: 10,055 Site admin

    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

  • allanallan Posts: 61,449Questions: 1Answers: 10,055 Site admin

    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

  • CapamaniaCapamania Posts: 229Questions: 79Answers: 5

    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!

This discussion has been closed.