PHP-Libraries. Require multiple columns to be unique as a set

dclar43

I could have sworn I had seen an example of this but I can't track it down.

Say my records have the columns A, B, C, D, E, F, G.
When I create a new record I want to ensure that no other records have the same values for, say, A, B, D.
Each A, B, D are not required to be unique individually, but they cannot occur in another record with those same values.

Is this a check that is supported in Editor? Or is there a way to validate this without just doing something like

$editor->sql("
    SELECT
        A, B, D, COUNT(*)
    FROM
        table
    GROUP BY
        A, B, D
    HAVING
        COUNT(*) > 1
        ");

Thanks for any help!

btree

Yeah its just there is one build in validator "unique".

https://editor.datatables.net/manual/php/validation

cheers
Hannes

dclar43

Hi btree,

Thanks for the input but as I mentioned

A, B, D are not required to be unique individually

The Validator() function operates on a field. Which means, as far as I can tell, you can only check for each individual field to be unique on it's own, not a group of fields to be unique as a set.

btree

Hi,

sorry misread your question.

Then i would do the same as you already mentioned, maybe with a function.

->validator( function ( $val, $data, $opts ) {
    return your_count($data) => 1 ? 'A,B,D must be a unique combination' : true;
} );

Cheers
Hannes

dclar43

Okay so this is what I've come up with so far

<?php
    include_once($_SERVER["DOCUMENT_ROOT"] . "/assets/Editor/php/DataTables.php");

    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;

    $editor = Editor::inst($db, "table")
        ->fields(
            Field::inst("id")->set(false),
            Field::inst("a")->validator("Validate::notEmpty"),
            Field::inst("b")->validator("Validate::notEmpty"),
            Field::inst("c")->validator("Validate::notEmpty"),
            Field::inst("d")->validator("Validate::notEmpty")
        )
        ->on("preCreate", function($editor, $values) use ($db) {
            /** \@var \DataTables\Editor $editor*/

            $result = $db
                ->query("select")
                ->table("db.table")
                ->get("id")
                ->where("a", $values["a"])
                ->and_where("b", $values["b"])
                ->and_where("c", $values["c"])
                ->and_where("d", $values["d"])
                ->exec();

            // In the case that we create a new fully unique entry fetchAll() will return 1
            // Does that mean that the entry has been created before we even check at this point?
            if(count($result->fetchAll()) > 1) {
                // Temporary debug output
                print("<br>Not Unique");
            }
        })
        ->process($_POST)
        ->json();

I have three more questions at this point:

1: How should I throw an error with this method? Should I just manually handle that by outputting

{fieldErrors: [{name: "FIELD", status: "Not unique blah blah"}], data: []}

If so is there a way to throw a general error rather than a field specific one?

2: As partially noted in a comment it seems that the new row is created BEFORE we finish our preCreate function. The count for fetchAll() will return 1 even when that SQL should return. Am I misinterpreting preCreate?

3: So let's say the previous two questions are addressed. What is the 'correct' method of canceling the creation of a new row? I would also like to do something similar when performing the delete/remove action. Rather than delete I would like to set the row's "Hidden" field to 1.

allan

Hi,

What you need here is a custom validation method. Although preCreate will execute your code, as you have found there is no way to exit from the code (I suppose you could in theory set all fields with ->set(false), but a validator is the way to do it!).

Regards,
Allan

dclar43

Hi Allan,

Thanks for the input. I'm still poking around trying to figure out the best way to implement this. I see \DataTables\Editor\Field::validator can take a function which is how I'm looking at doing this, but that would still leave me with an ugly hack like:

include_once($_SERVER["DOCUMENT_ROOT"] . "/assets/Editor/php/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

$conflictArray = [];

$editor = Editor::inst($db, "table")
    ->fields(
        Field::inst("id")->set(false),
        Field::inst("a")->validator("Validate::notEmpty")->validator(function($val, $data, $field) use($db, $conflictArray) {
            $results = $db
                ->query("select")
                ->table("db.table")
                ->get("id")
                ->where("a", $val)
                ->exec()
                ->fetchAll();

            foreach($results as $res) {
                (in_array($res["id"], $conflictArray) ? $conflictArray[$res["id"]]++ : $conflictArray[$res["id"]] = 1);
            }
        }),
        Field::inst("b")->validator("Validate::notEmpty")->validator("REPEAT HERE"),
        Field::inst("c")->validator("Validate::notEmpty")->validator("REPEAT HERE"),
        Field::inst("d")->validator("Validate::notEmpty")->validator("REPEAT HERE. Check if any id has a counter of 4. if so that mask has tripped the check for all fields.")
    )
    ->process($_POST)
    ->json();

or

$editor = Editor::inst($db, "table")
    ->fields(
        Field::inst("id")->set(false),
        Field::inst("a")->validator("Validate::notEmpty")->validator(function($val, $data, $field) use($db) {
            $result = $db
                ->query("select")
                ->table("db.table")
                ->get("id")
                ->where("a", $data["a"])
                ->and_where("b", $data["b"])
                ->and_where("c", $data["c"])
                ->and_where("d", $data["d"])
                ->exec();

            return(count($result->fetchAll()));
        }),
        Field::inst("b")->validator("Validate::notEmpty"),
        Field::inst("c")->validator("Validate::notEmpty"),
        Field::inst("d")->validator("Validate::notEmpty")
    )
    ->process($_POST)
    ->json();

That's off the top of my head. Some slightly cleaner ways to do that but still, aiming for something cleaner.

However if the action is Editor::ACTION_EDIT I then need to determine the values of all of the other fields. I don't see a way to do that either since, at that point, I only have the field's name. I can't use my editor instance obviously, I don't see a way to get this information from $field,~~I don't have an id to work with either.~~(why no strike-through?) Not sure what I was doing wrong but row_X wasn't being included in the sent data.

Is there a more straightforward way of doing this that I'm missing? I dug around and wasn't able to see a way to register validator of my own for use with ->validator("funcName");.

Update: Found out that an issue was causing row_X not to be sent, it was undefined/null. Another issue is that when using inline editing ONLY the field being edited is sent. If you use the main editor it sends everything.

allan

That looks good to me. Why do you think it is a hack? The only way I can see it could be tidied up would be to create a custom validation class, or augment the existing one, and add your validation method to it so you can all it like the pre-build validators.

Another issue is that when using inline editing ONLY the field being edited is sent

This upgrade document details that behaviour, why and how to change if you don't want it.

Allan

dclar43

Thanks for the upgrade doc, I had missed that. I guess by 'hack' I mean 'not as clean as I would like.' I'm on my phone atm so I can't post my current solution. I more or less made a custom validate class to use.

Thanks again for all of your help Allan.