select options from sql source

select options from sql source

crush123crush123 Posts: 417Questions: 126Answers: 18

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()

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    edited March 2017 Answer ✓

    Yes :smile:.

    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

  • crush123crush123 Posts: 417Questions: 126Answers: 18

    ...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, '=' )
    
  • crush123crush123 Posts: 417Questions: 126Answers: 18

    ..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, '=' )
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Answer ✓

    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

  • crush123crush123 Posts: 417Questions: 126Answers: 18

    Even better !

    Cheers

This discussion has been closed.