Add Options in Database

rf1234

Just wanted to share this solution to add options that are saved in a database when users enter new options. I hadn't found a solution for this with the PHP options instance because it only seems to be able to read and render existing options from the database but does not create them. @allan what is your view? Did I overlook anything?

Anyway, here is my solution:

First the Javascript (it is only the field definition that needed to be adjusted):

var Editor .....,
table: "#theTable",
fields: [ {
        label: "Provides financing:",
        name:  "contract.gov_manual_creditor_id", //render creditor name
        type: "selectize", 
        opts: {
            create: true,
            createFilter: function(val) {
              return ( isNaN(val) || val.indexOf('.') > -1 ) ? val : false;
            },
            maxItems: 1,
            maxOptions: 15,
            openOnFocus: false,
            allowEmptyOption: false,
            placeholder: "Please select a Counterparty or add one",                
            render: {
                option_create: function(data, escape) {
                    return '<div class="create">Neu <strong>'
                           + escape(data.input) + '</strong>&hellip;</div>';
                }
              }
            },
    }, ....

I added the following to what I previously had:
- I allowed the creation of new entries ( create: true)
- I defined a createFilter that does not allow any integer number as a new option (because that would be mistaken as an id). So the number "2" wouldn't be allowed while "2.0" or even "2." would be ok.
- new render object too in order to provide a different text for new entries. Here it is (German) "Neu" as opposed to the default "Add"

On the server side the options instance remained unchanged. Here it is for completeness:

Field::inst( 'contract.gov_manual_creditor_id' )
    ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
    ->options( Options::inst()
        ->table('gov_manual_creditor')
        ->value( 'id' )
        ->label( 'name' )
        ->order( 'name asc' )
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {
            $q ->where('gov_id', $_SESSION['gov_id']);
        } )
    ),

I added two new event listeners 'writeEdit' and 'writeCreate' which refers to the parent table not to the table that contains the options. Here I check whether the field passed from the client 'contract.gov_manual_creditor_id' actually contains an id or a new string entered by the user. If it contains a string instead of the id (and the users cannot enter an integer number at the front end) I insert that value into the options table and update the foreign key in the parent table as well which previously was filled with NULL by the options instance.

->on( 'writeCreate', function ( $editor, $id, $values ) {
    $manualId = $values['contract']['gov_manual_creditor_id'];
    if ( ( ! is_numeric( $manualId ) ) || 
         ( count( explode('.', $manualId) ) > 1 ) ) { //strpos didn't work with a period!!
        processNewManualCreditor( $editor->db(), $id, $manualId );
    }
} )
->on( 'writeEdit', function ( $editor, $id, $values ) {
    $manualId = $values['contract']['gov_manual_creditor_id'];
//if we have periods in the string it shouldn't be considered as numeric!!
    if ( ( ! is_numeric( $manualId ) ) || 
         ( count( explode('.', $manualId) ) > 1 ) ) { //strpos didn't work with a period!!
        processNewManualCreditor( $editor->db(), $id, $manualId );
    }
} )

and this is the function I call. I am using the Data tables database handler in order to avoid locking conflicts that I experienced using my own handler.

function processNewManualCreditor( $db, $id, $manualId ) {
    $result = $db->insert( 'gov_manual_creditor', array (
        'gov_id'     => $_SESSION['gov_id'],
        'name'       => $manualId,
        'updater_id' => $_SESSION['id'],
        'creator_id' => $_SESSION['id']                    
    ), array ( 'id' ) );
    
    $db->raw()
       ->bind( ':fk', $result->insertId() )
       ->bind( ':id', $id )
       ->exec( 'UPDATE contract   
                   SET gov_manual_creditor_id = :fk   
                 WHERE id = :id' );
}

Any ideas? Maybe a better solution? Thanks for letting me know!

rf1234

@allan : is there any other solution available to add options with a PHP options instance? I think my solution above is only a work around ...

allan

That's a nice way of doing it - thanks for sharing your solution.

You haven't overlooked anything - adding options to a select list is not a feature of Editor at this time. It is something I plan to do in future (effectively nested Editor windows), but for now this kind of workaround, or using something like Select2 which does allow for new tags to be created, would be the way to do it.

Allan

rf1234

Good to know that Select2 can also do this. I got used to Selectize which is perfect for this in my opinion. Had performance issues with Select2 a while ago.

dataB

This is great stuff! For anyone searching for a simple way to allow users to manually add values to Select2/Selectize I found that Selectize will work for adding/removing values without an additional processing function for a simple join. My code below:

Javascript:

{ label: 'Box Barcode(s)',  type: 'selectize',  name: 'firstaid_boxes[].barcode',
     opts: { maxItems: null, create: true, allowEmptyOption: false,
          placeholder: 'Select or create barcodes...'}
},

PHP:

->join(
        Mjoin::inst( 'firstaid_boxes' )
            ->link( 'accounts.id', 'firstaid_boxes.account_id'  )
            ->order( 'firstaid_boxes.barcode asc' )
            ->fields(
            Field::inst( 'barcode' )
                ->options( Options::inst()
            ->table( 'firstaid_boxes' )
            ->value( 'barcode' )
            ->label( 'barcode' )
            )
        )
        )   

I haven't been able to get this to work with Select2 so I'm indebted to @rf1234 for coming up with his solution!