Unique validation for multiple columns

Kuo7

Hi,
I have a table that gets data from the selections of two (2) drop-down menus: drop-down of teachers and drop-down of subjects. I want unique validation for both which make up one (1) row, as in a teacher associated with a subject should not be associated with that subject more than once. I believe unique validation for one (1) column is possible through the use of the unique validation method. I have come to the belief that Global Validators is the way to go in solving my kind of problem but I'm not sure exactly as to how to start as there were not many examples/discussions about it.
Can anyone point me in the right direction, please? Thank you in advance.

allan

You are exactly right - the global validator is the way to do this. Rather than looking for forum discussions about it, the reference documentation would probably be a better option. There are examples in the docs there showing the basics of how to use a custom validator, so the missing piece is how to query the DataTable. You can use Editor's DB libraries for that. For example $db->select( 'table', 'fields', [ where... ] )->fetch(); will do a simple SELECT query.

Allan

Kuo7

Thank you Allan. I have modified my PHP file as per your recommendation. Now, I kept getting the An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3-AGR-2019' for key 'PRIMARY' error when entering a combination of values that already exists. This makes sense as my composite key is made up of all three (3) fields of the teachers_subjects table.
I'm thinking the error-checking precedes the global validation. I would really love for the global validation to run and thus return the message within, before the errror-checking do its thing.
My server-side script is as follows:
```php
<?php
//DataTables PHP library to be used with Editor
include('Editor-PHP-1.9.0/lib/DataTables.php');
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

//use DataTables\Editor;
Editor::inst($db, 'teachers_subjects', array('teacher', 'subject', 'year'))
    ->fields(
        Field::inst('teachers_subjects.teacher')
            ->options(Options::inst()
                ->table('teachers')
                ->value('id')
                ->label('address')
                )
            ->validator(Validate::dbValues())
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Please select teacher.')
        )),
        Field::inst('teachers.address'),
        Field::inst('teachers_subjects.subject')
            ->options(Options::inst()
                ->table('subjects')
                ->value('code')
                ->label('name')
            )
            ->validator(Validate::dbValues())
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Please select subject.')
        )),
        Field::inst('subjects.name'),
        Field::inst('teachers_subjects.year')
    )
    ->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('count(*)')->table('teachers_subjects')
                            ->where('teachers_subjects.teacher', $values['teachers_subjects']['teacher'])
                            ->where('teachers_subjects.subject', $values['teachers_subjects']['subject'])
                            ->exec()
                            ->fetch();
                if ($count == 1){
                    return 'This selection is already in the database.';
                }
            }
        }
    })
    ->leftJoin('teachers', 'teachers.id', '=', 'teachers_subjects.teacher')
    ->leftJoin('subjects', 'subjects.code', '=', 'teachers_subjects.subject')
    ->process($_POST)
    ->json();

<?php > ``` Thank you in advance. ?>

allan

Getting an SQL error message like that means that your validation isn't resulting in an error.

I think you might need to use:

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

Note that change to get('*') (might want to change that to a single column to save a bit of memory) and also the use of ->count().

Allan

Kuo7

Thank you so much Allan! This solves my problem.