Editor - add record problem

mmscot

Hello,

I have code that works to display data from a child table (Rebuilds), and add a new child record when the parent record (Pumps) key exists. If the key does not exist, I get the error

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (pumpschema.rebuilds, CONSTRAINT rebuilds_ibfk_3 FOREIGN KEY (pumps_pump_serial_nbr) REFERENCES pumps (pump_serial_nbr) ON DELETE CASCADE ON UPDATE CASCADE)

Makes sense. Next I added a new function in Editor Validate.php to display a clear user error message "Pump serial number does not exist", but I get "Error" displaying on the Add screen instead, and in browser debug XHR {"fieldErrors":[{"name":"pumps_pump_serial_nbr","status":null}]}

This is the function I created in Validate.php: (duplicated the function "unique" at the bottom of the file and modified)

    public static function validpumpserialnbr( $val, $data, $opts, $host ) {
        $cfg = Validate::_extend( $opts, null, array(
            'message' => 'Pump serial number does not exist',
            'db'      => null,
            'table'   => null,
            'field'   => null
        ) );
        
        $common = Validate::_common( $val, $cfg );
        if ( $common !== null ) {
            return $common;
        }

        $editor = $host['editor'];
        $field = $host['pumps_pump_serial_nbr'];

        $db     = $cfg['db']    ? $cfg['db']    : $host['db'];
        $table => 'pumps'; 
        $column => 'pump_serial_nbr';

        $query = $db
            ->query( 'select', $table )
            ->get( $column )
            ->where( $column, $val );

            $res = $query->exec();

        return $res->count() === 0 ?
            true :
            $cfg['message']; 
    }

And am calling it from PHP:

<?php
include( "lib/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;
Editor::inst( $db, 'rebuilds', 'rebuild_id' )
    ->fields(
        Field::inst( 'pumps_pump_serial_nbr' )
            ->validator( 'Validate::validpumpserialnbr' ),
        Field::inst( 'customer' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'date_received' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'Y-m-d' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'Y-m-d' )
            ->setFormatter( 'Format::date_format_to_sql', 'Y-m-d' ),
        Field::inst( 'fail_reasons' ),
        Field::inst( 'qb_quote_nbr' )
    )
    ->process( $_POST )
    ->json();

Please help.

Scott

allan

Hi Scott,

What there a reason to duplicate the unique method and rename it rather than juse using the build in options? You could do:

->validator( 'Validate::unique', array(
  'message' => 'Pump serial number does not exist',
  'table' => 'pumps',
  'column' => 'pump_serial_nbr'
) )

If you do that, what do you get? Also when you say you get "Error" - what error message do you get? What is the JSON returned from the server?

Allan

mmscot

I did not think about doing it this way, makes sense now. I made the change in the PHP code as suggested, and now get the JSON error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pumps_pump_serial_nbr' in 'field list'

Scott

allan

Should

Field::inst( 'pumps_pump_serial_nbr' )

Actually be:

Field::inst( 'pump_serial_nbr' )

?

Allan

mmscot

Hi Allan,

I tried using the code above - Field::inst( 'pump_serial_nbr' ) - and got the JSON error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pump_serial_nbr' in 'field list'

Parent Table "Pumps" contains pump_serial_nbr (primary key)

Child Table "Rebuilds" has the foreign key pumps_pump_serial_nbr

I am trying to add a rebuild record, and the serial number for the Rebuild record must first exist in Pumps.

Scott

allan

Hi,

Okay, given that the field is for the rebuilds table it should be pumps_pump_serial_nbr (which you had before - apologies).

I've just realised my mistake! Rather than using column in the array of options it should be field :-)

->validator( 'Validate::unique', array(
  'message' => 'Pump serial number does not exist',
  'table' => 'pumps',
  'field' => 'pump_serial_nbr'
) )

Next time, I'll read my own documentation!

Allan

mmscot

I'm getting results as expected now. However, I can't seem to put logic into place to use the 'unique' validator to check for an existing value. I need a solution for a 'not unique' scenario where processing will continue. Can you provide a solution for use with the 'unique' validator?

Scott

mmscot

Fixed the problem by adding a function in Validate.php called valueExists (duplicate of the function "unique"). I just changed the message text, and the "return $res" to:

if ($res->count() === 0) {
return $cfg['message'];
} else {
return true;
}

Thanks for your help, Allan, and for the great UI.

Scott

allan

Hi Scott,

Good to hear you got a working solution.

Could you clarify what you mean by "I need a solution for a 'not unique' scenario where processing will continue"? What processing do you want to happen when a non-unique value is found (at the moment an error message should be returned).

Allan

mmscot

I am referring to a one to many relationship where the parent must exist before the child can be added (physical pump must exist before you can rebuild it).

allan

Got it - thanks for the clarification!

Allan