Complex Where condition stops Datatable update.

jasoncaditrack

I have written a PHP server script to read & update my database table, using the Editor class.

In my initial implementation, I used a simple where clause Editor->where() in the PHP Editor::inst to select only a single record type of from my table:

  ->where(  'twork_src.src_type' , 'PS' )

and the Editor update performed as expected, updating just the 2 table columns in the database and updating the datatable table, using the 2 new field values in my form ('twork_src.status' and 'twork_src.comment').

When I changed the simple where condition, to a complex where condition, to select 2 record types ,

->where( function ( $q ) {
      $q->where(  'twork_src.src_type' , 'PS' );
      $q->or_where(  'twork_src.src_type' , 'BL' );
    } )

the database is still correctly updated by Editor, but the datatable is no longer updated. I have to re-draw the table to see the updated values.

I see in the documentation that
**When writing data (create and edit actions) you should use the Field->set() and Field->setValue() methods. **

But I can find neither details of how to use them, nor any examples, and don't understand how to incorporate this into my script exactly.

Can anyone give me an example of how the Field->set() method could be used to address this issues, or am I misunderstanding the cause of the problem.

Many thanks!

Below, I show the Editor:inst that doesn't work correctly.

// DataTables PHP library and database connection
include( "../../Editor-2.0.8/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;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'twork_src' )
    ->fields(
        Field::inst( 'twork_src.wo_num' ),
        Field::inst( 'twork_src.src_date' ),
        Field::inst( 'twork_src.src_number' ),
        Field::inst( 'twork_src.src_comment' ),
        Field::inst( 'twork_src.src_reference' ),
        Field::inst( 'twork_src.src_type' )
           ->set( Field::SET_NONE),
        Field::inst( 'twork_src.staff_id' )
            ->options( Options::inst()
                    ->table( 'tstaff' )
                    ->value( 'id' )
                    ->label( 'staff_name')
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'tstaff.staff_name' ),
        Field::inst( 'twork_src.status' ),
        Field::inst( 'twork_src.id' )
            ->options( Options::inst()
                    ->table( 'tlots' )
                    ->value( 'lot_src_id' )
                    ->label( 'lot_alpha', 'lot_number','item_id')
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'tlots.lot_alpha' ),
        Field::inst( 'tlots.lot_number' ),
        Field::inst( 'tlots.item_id' )
      ->options( Options::inst()
          ->table( 'titems' )
          ->value( 'id' )
          ->label( 'item_num','item_name')
      )
      ->validator( Validate::dbValues() ),
      Field::inst( 'titems.item_num' ),
        Field::inst( 'titems.item_name' ),
    )
    ->leftJoin( 'tlots', 'tlots.lot_src_id', '=', 'twork_src.id' )
    ->leftJoin( 'titems', 'titems.id', '=', 'tlots.item_id' )
    ->leftJoin( 'tstaff', 'tstaff.id', '=', 'twork_src.staff_id' )
  ->where( function ( $q ) {
          $q->where(  'twork_src.src_type' , 'PS' );
          $q->or_where(  'twork_src.src_type' , 'BL' );
        } )
    ->process( $_POST )
    ->json();

colin

According to the manual, it looks like your syntax is wrong - please could you try this instead:

->where( function ( $q ) {
      $q->where( function ( $r ) {
         $r->where(  'twork_src.src_type' , 'PS' )
         $r->or_where(  'twork_src.src_type' , 'BL' );
      } );    
} )    

Colin

jasoncaditrack

Thanks Colin, you saved me again! Your solution works perfectly.

I had not appreciated the nested function in the grouping condition. Can you tell me what the $r argument of the second function represents exactly?

FYI I have been using Editor for about 2 weeks now, and I just want to say that the documentation is really the most structured and informative resource I have ever come across for this kind of plug-in/framework. It was a steep learning curve, but the documentation made it very achievable.
Now I see what can be done with Datatables & Editor it was well worth the effort.
Thanks again for your help.

colin

Thanks for the kind words. $r is still an Editor instance but used for the grouping of the conditions,

Colin