select options from sql source

crush123

i want to add a select list to my editor, but the only values i want available are those which are not already in the target table.

eg.

SELECT ColourID AS Value, ColourDescription AS Label FROM refcolour WHERE ColourID NOT IN (SELECT ColourID FROM tblproductcolour WHERE ProductID = ?)

is there a way to do this in version 1.6x without first creating a view ?

on earlier versions of editor I would create a select list separately in my ajax/json source and update the editor field on initComplete()

allan

Yes .

You can use the Options class to do this:

->options( Options::inst()
  ->table( 'refcolour' )
  ->value( 'ColourID' )
  ->label( 'ColourDescription' )
  ->where( function ( $q ) ) {
    $q->( 'ColourID', '(SELECT ColourID FROM tblproductcolour WHERE ProductID = :productId)', 'NOT IN', false );
    $q->bind( ':productId', ... );
  } )
)

The documentation for the sub select is actually in the conditions part of the documentation.

The bit to fill out is the :productId binding value.

Allan

crush123

...nearly there.

I got this to work when i set the bind value to an integer, but when i pass in a variable ($filter), which is the same variable used in the where clause, I get an error 'Notice: Undefined variable: ' on line 31 (line 16 in the snippet below)

$filter = isset($_POST['ProductID'])?$_POST['ProductID']: '1';
// Build our Editor instance and process the data coming from _POST
  $data = Editor::inst( $db, 'tblproductcolour', 'ProductColourID' )//table name and PKey(defaults to ID)
->field(
    Field::inst( 'tblproductcolour.ColourID' ),
    Field::inst( 'tblproductcolour.Price' )
        ->validator( 'Validate::numeric' ),
    Field::inst( 'tblproductcolour.Display' ),
    Field::inst( 'refcolour.ColourID' )
    ->options( Options::inst()
    ->table( 'refcolour' )
    ->value( 'ColourID' )
    ->label( 'ColourDescription' )
    ->where( function ( $q ) {
    $q->where( 'ColourID', '(SELECT ColourID FROM tblproductcolour WHERE ProductID = :productId)', 'NOT IN', false );
    $q->bind( ':productId', $filter );
            }
        )
    ),
    Field::inst( 'refcolour.ColourDescription' )
        )

->leftJoin( 'refcolour', 'refcolour.ColourID', '=', 'tblproductcolour.ColourID' )
->where( 'tblproductcolour.ProductID', $filter, '=' )

crush123

..fixed it.

i created a second filter variable from the post value to use for the options

      $filter = isset($_POST['ProductID'])?$_POST['ProductID']: '1';
  //$filter2 = isset($_POST['ProductID'])?$_POST['ProductID']: '1';
// Build our Editor instance and process the data coming from _POST
  $data = Editor::inst( $db, 'tblproductcolour', 'ProductColourID' )//table name and PKey(defaults to ID)
->field(
    Field::inst( 'tblproductcolour.ColourID' ),
    Field::inst( 'tblproductcolour.Price' )
        ->validator( 'Validate::numeric' ),
    Field::inst( 'tblproductcolour.Display' ),
    Field::inst( 'refcolour.ColourID' )
    ->options( Options::inst()
    ->table( 'refcolour' )
    ->value( 'ColourID' )
    ->label( 'ColourDescription' )
    ->where( function ( $q ) {
    $filter2 = isset($_POST['ProductID'])?$_POST['ProductID']: '1';
    $q->where( 'ColourID', '(SELECT ColourID FROM tblproductcolour WHERE ProductID = :productId)', 'NOT IN', false );
    $q->bind( ':productId', $filter2 );
            }
        )
    ),
    Field::inst( 'refcolour.ColourDescription' )
        )

->leftJoin( 'refcolour', 'refcolour.ColourID', '=', 'tblproductcolour.ColourID' )
->where( 'tblproductcolour.ProductID', $filter, '=' )

allan

If you have a variable you want to use in an anonymous function in PHP you have to make use of the use keyword:

->where( function ( $q ) use ( $filter ) {

Maddening syntax, but that's how PHP works.

Allan

crush123

Even better !

Cheers