using Editor how to implement : WHERE NOT IN (SELECT...

tron2101

I'm trying to convert the following to Editor::inst(...

```SELECT
tblunits.id,
tblunits.unitnumber
FROM
tblunits
WHERE
tblunits.id NOT IN (SELECT tblcamping.fkunit FROM tblcamping)

weedenb

For anything other than the basic =<> conditionals I had to use the query closure functions instead of the direct Editor->Where. Not sure about the rest of your subselect but the answer to that is probably in the closure functions also?

This is what I've come up with, One dumb gotcha I forgot about was that the php global variables are not carried into the functions so you have to be sure and use the $GLOBALS[' '] reference inside the datatables functions (or use the $_GET/POST parameters directly). I'm using an 'IN' conditional here to pull both an option list from a reference table and filter the primary table rows I'm editing, I assume a 'NOT IN' should work similar?

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


// Build our Editor instance and process the data coming from _POST

$muckids = '(0)'; // just so I don't pass an empty list

if ( isset($_GET['muckids']) ) {
    $muckids = $_GET['muckids'];
}


$editor = Editor::inst( $db, 'haul', 'id' )
    ->fields(
        Field::inst( 'hid' )
            ->validator( 'Validate::numeric' )
            ->options( 'turnover', 'id', 'heading', function ($q) {
                $q->where( 'id', $GLOBALS['muckids'], 'IN', false);
            } ),
        Field::inst( 'heading' ),
        Field::inst( 'fromto' )
            ->options( 'list_locations', 'label', 'label' ),
        Field::inst( 'operator' ),
        Field::inst( 'equipment' )
            ->options( 'truckfactors', 'equipment', 'equipment' ),
        Field::inst( 'loads' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::nullEmpty' ),
        Field::inst( 'material' )
    );
//Filter for matching hid's
$editor->where( function ( $q ) {
    $q->where( 'hid', $GLOBALS['muckids'], 'IN', false);
  });

$editor
    ->process( $_POST )
    ->json();

Corresponding javascript ajax call looks like:

ajax: 'php/haul.php?muckids='+muckids,

allan

For anything other than the basic =<> conditionals I had to use the query closure functions instead of the direct Editor->Where.

This is correct. More advanced conditions have to use a closure as shown in the documentation. The closure provides access to the underlaying Query which allows more control over the conditions applied.

Allan