validate editor value to ensure SUM of column > 0

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
Hi, I have setup a test parent child as per your blog article https://datatables.net/blog/2019-01-11#Server-side-(PHP) to simplify a problem I am experiencing on my website. I have added a column to the users table called orderlines.

I need to put some validation in the server or client file to summate the Orderlines column for the given site, Exeter in this example, and then issue an error if a new value results in the new summation being < 0

So in the example above the SUM Orderlines would be 1000, so an Orderline entry of -1001, -1002 etc. should display an error to the user e.g.
orderlines exceeded your holding

server file

<?php

// DataTables PHP library
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst( $db, 'users' )
        ->field(
            Field::inst( 'users.first_name' ),
            Field::inst( 'users.last_name' ),
            Field::inst( 'users.phone' ),
            Field::inst( 'users.site' )
                ->options( 'sites', 'id', 'name' )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'sites.name' ),
            Field::inst( 'users.orderlines' )
        )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
        ->where( 'site', $_POST['site'] )
        ->process($_POST)
        ->json();
}

I have used the $count construct to provide validation in another example of another server file below to prevent editing a parent which has child lines, but I need to perform a SUM in this instance.

    ->validator( function ( $editor, $action, $data ) use ($userid) { //1
        global $count;

        if ( $action === Editor::ACTION_EDIT ) { //2
           foreach ( $data['data'] as $pkey => $values ) { //3

                    $count = $editor
                            ->db()
                            ->query('select')
                            ->get('*')
                            ->table('dm_holdinglines')
                            ->where( function ( $q ) use ( $userid ) {
                            $q->where( 'user_id', $userid );
                            } )
                            ->and_where( 'holdings_id', $values['dm_holdings']['id'] )
                            ->exec()
                            ->count();
                           
                    if ($count > 0){ //4
                        return 'cannot edit holdings with ' . $count . ' holding lines defined';
                    } //4

           } //3

        } //2
    } ) //1

The client file is very similar to the blog article with the addition of the orderlines column.
extract of client file with changes

    var usersEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: "../../Editor-PHP-2.0.5/controllers/users_g_b.php",
            data: function(d) {
                d.site = rowData.id;
            }
        },
        table: table,
        fields: [
            {
                label: "First name:",
                name: "users.first_name"
            },
            {
                label: "Last name:",
                name: "users.last_name"
            },
            {
                label: "Phone #:",
                name: "users.phone"
            },
            {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location",
                def: rowData.id
            },
            {
                label: "Orderlines:",
                name: "users.orderlines"
            },
        ]
    });

    // Child row DataTable configuration, always passes the parent row's id to server
    var usersTable = table.DataTable({
        dom: "Bfrtip",
        pageLength: 5,
        ajax: {
            url: "../../Editor-PHP-2.0.5/controllers/users_g_b.php",
            type: "post",
            data: function(d) {
                d.site = rowData.id;
            }
        },
        columns: [
            { title: "First name", data: "users.first_name" },
            { title: "Last name", data: "users.last_name" },
            { title: "Phone #", data: "users.phone" },
            { title: "Location", data: "sites.name" },
            { title: "Orderlines", data: "users.orderlines" }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: usersEditor },
            { extend: "edit", editor: usersEditor },
            { extend: "remove", editor: usersEditor }
        ]
    });

I can provide access to my website to test if required.

any example or advice would be appreciated.

Many Thanks Colin

cpshart

This thread may contain my answer

https://datatables.net/forums/discussion/comment/160624/#Comment_160624

I will report back if I have been able to integrate the snippet into my code successfully.

Thanks Colin