Mjoin with single select dropdown

rf1234

I am using mjoin with selectize at the front end (I also tried this with the built-in select field and select2 - same results). It might be an odd way to use this but I only want to allow the selection of one item by the user. I was surprised that Editor kept crashing if I limit selection to just one item. When I changed max Items to 2 it worked fine even if the user actually selected nothing or just 1 record. Just the limitation to 1 record caused the crash.

I found a solution for this looking at what the front end sends to the server in both cases and changed the way things are being sent for single select.

For multiple select Editor sends an array of ids to the server looking like this
d.data["row_xx"].yourName[0].id
In addition this is being sent as well:
d.data["row_xx"]["YourName-many-count"]

If you limit maxItems to 1 something completely different is being sent to the server and Editor crashes:
d.data["row_xx"].yourName
which is a string and contains the single id.
No "many count".

This is my solution working for single and multiple select as well:

var editor = new $.fn.dataTable.Editor( {
   ajax: {
        url: 'actions.php?action=tblContractEntryGov',
        data: function ( d ) {
    //make selectize work with an mjoin and single select!
            if (d.action === 'create' || d.action === 'edit') {                
                var keys = Object.keys(d.data); //array of object keys
                var rowId = keys[0]; //row_xxx (edit) or 0 (create)
                if ( typeof d.data[rowId].base !== 'undefined' ) {                    
                    var baseId = d.data[rowId].base;
                    if ( typeof baseId === 'string' || baseId instanceof String) {
                        var base = []; var element = {};
                        element.id = baseId; //set property id of element object
                        base.push(element); //put element object into base array
                        d.data[rowId].base = base; //set new property of object and put the array into it
                        d.data[rowId]["base-many-count"] = 1; //set the many count as well
                    }
                }
            }
        }
    },

This is my Editor field definition:

{
    label: lang === 'de' ? 
            'Optional, Auswahl Basisgeschäft:' : 
            'Optional, Select of base contract:',
    name: "base[].id", //render gov_name, govdept_name
    type: "selectize", 
    opts: {
        create: false,
        maxItems: 1,
        openOnFocus: true,
        allowEmptyOption: true,
        placeholder: lang === 'de' ? 
            'Bitte maximal ein Basisgeschäft wählen (optional)' : 'Please select one base contract maximum (optional)'
        }
}, {    

This is the mjoin on the server side:

//manually entered contracts may have one base contract assigned
//this serves the purpose of proper netting of internal contracts with 
//departments
->join(
Mjoin::inst( 'base' )
    ->link( 'contract.id', 'internal_deal_has_base_contract.internal_deal_contract_id' )
    ->link( 'base.id', 'internal_deal_has_base_contract.base_contract_id' )
    ->order( 'serial, instrument, type asc' )
    ->fields(
        Field::inst( 'id' )->set( false )
            ->options( Options::inst()
                ->table( 'base' )
                ->value( 'id' )
                ->label( array('serial', 'instrument', 'type', 'number') )
            //render serial, instrument, type, number
                ->render( function ( $row ) {               
                    return '# '.$row['serial']
                            .' / '.renderInstrument($row['instrument'])
                            .' / '.renderType($row['type'])
                            .' ('.$row['number'].')';
                } )
                ->order( 'serial, instrument, type asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                    $q ->where( function($r) {
                        $r ->where('govdept_id', $_SESSION['govdept_id'] );
                        $r ->where('instrument', 'Z', '<' );
                    });
                } )
            ),
        Field::inst( 'serial' )->set( false ),
        Field::inst( 'instrument' )->set( false ),    
        Field::inst( 'type' )->set( false ),
        Field::inst( 'number' )->set( false )
    )
)                

@allan, did I miss anything? Do you think it is worth enabling something like this for an mjoin? (I definitely need it because I want the link table to be set by the mjoin.)

allan

This is the condition that Editor uses to check it if should add the -many-count parameter (which tells the server-side that its an array of data, regardless of if there are 0, 1, 2 or more entries submitted):

$.isArray( value ) && name.indexOf('[]') !== -1

The second part will pass due to name: "base[].id", so its the $.isArray that we need to check. When you use the built in select field type, did you set the multiple option? That should force it always to return an array, regardless of how many are selected.

My thinking is that Selectize will automatically take a maxItems of 1 to mean that you want a scaler value back - looking at the Selectize code I see:

self.settings.mode = self.settings.mode || (self.settings.maxItems === 1 ? 'single' : 'multi');

so yes, this is effectively a limitation in Selectize. It would be possible to add a multiple option to the Selectize plug-in for Editor to workaround this (convert it to an array).

Allan

rf1234

Hi Allan,

ok, I understand that. But the issue is exactly the same using your built in field type.

I must use this in order to avoid the mjoin to crash:

type: "select",
multiple: true,

How can I limit selection to 1 item while at the same time setting "multiple" to true? I don't think this is possible hence I would consider this behavior to be a bug of the mjoin ...

I tested with "Select", "Selectize" and "Select2": The results were identical.

Roland

P.S.: Maybe I wasn't clear enough: My problem is that with an mjoin I am unable to limit selection to 1 because all of the three solutions will NOT return the format mjoin expects if I limit selection to 1 which is a business requirement. It is no solution to just allow the selection of multiple items.

Regarding selectize: It is sufficient to change this single line to avoid the mjoin to crash:

 maxItems: 2,

But again: The business requirement is to limit selection to 1 item ...

rf1234

Just tested this using my modification of what is being sent to the server:

}, {
  label: lang === 'de' ? 
           'Optional, Auswahl Basisgeschäft:' : 
           'Optional, Selection of base contract:',
  name: "base[].id", //render gov_name, govdept_name
  type: "select"
}

Works perfectly not only with "Select" but also with "Selectize" and "Select2".

Then I ran the same code without my modification. Please see the result from the attached screenshot.

allan

Good to hear you have a workaround - thanks for posting back.

ok, I understand that. But the issue is exactly the same using your built in field type.
I must use this in order to avoid the mjoin to crash:

Yes, that is expected and correct. If you want to force the select field type to use arrays, you must specify the multiple option as I mentioned above.

How can I limit selection to 1 item while at the same time setting "multiple" to true? I don't think this is possible hence I would consider this behavior to be a bug of the mjoin ...

Ah - I see the disconnect and its a mistake on my part, sorry. I had thought that if you added a size attribute set to be 1 (as well as the multiple) then it would limit it to 1, but that isn't the case. So yes, there is a limitation in the software there and I'll need to have a think about!

Allan

rf1234

ok, thanks!

My code below might not be the most sophisticated solution but it effectively emulates a multi-select situation even if a single-select was implemented by the developer. It also works if the developer implemented a multi-select because then nothing is changed due to the check for "string". So either way the back end receives a valid multi-select situation and all works fine. Another advantage is that it works for "select", "selectize" and "select2".
So why not implement it this way or another in the Editor front end?!

if (d.action === 'create' || d.action === 'edit') {               
    var keys = Object.keys(d.data); //array of object keys
    var rowId = keys[0]; //row_xxx (edit) or 0 (create)
    if ( typeof d.data[rowId].base !== 'undefined' ) {                   
        var baseId = d.data[rowId].base;
        if ( typeof baseId === 'string' || baseId instanceof String) {
            var base = []; var element = {};
            element.id = baseId; //set property id of element object
            base.push(element); //put element object into base array
            d.data[rowId].base = base; //set new property of object and put the array into it
            d.data[rowId]["base-many-count"] = 1; //set the many count as well
        }
    }
}

allan

So why not implement it this way or another in the Editor front end?!

Because in some cases you do want the current behaviour. In your specific case you don't, and I absolutely see why that is. I'll have a think about how best to implement that as it is something that I can see being useful.

Allan

rf1234

I fully agree, Allan!
For a "normal" single select with a foreign key (without link table), and an options instance you will still need the current behavior. My "solution" only works for the mjoin situation.

So this will still need the current behavior:

Field::inst( 'report.report_type_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
    ->options( Options::inst()
        ->table('report_type')
        ->value('id')
        ->label( array('label', 'number') )  //we need to get number into the field list otherwise SQL error with the server MySQL-Version. 
        ->render( function ( $row ) { 
            return $row['label']; 
        } )
        ->order('number asc')
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {                    
            $q ->where( 'user_id', $_SESSION['id'] );
        } )
    ),

rf1234

and another one ... my "solution" was incomplete. I had a crash when nothing was selected. In that case d.data[rowId].base is still a string but an empty one ... In that case it must be deleted because otherwise Editor tries to insert into the link table which causes an integrity violation. Editor doesn't care whether many count is 0, the variable itself also must be deleted.

if (d.action === 'create' || d.action === 'edit') {                
    var keys = Object.keys(d.data); //array of object keys
    var rowId = keys[0]; //row_xxx (edit) or 0 (create)
    if ( typeof d.data[rowId].base !== 'undefined' ) {                    
        var baseId = d.data[rowId].base;
        if ( typeof baseId === 'string' || baseId instanceof String) {
            if (baseId > '0') {
                var base = []; var element = {};
                element.id = baseId; //set property id of element object
                base.push(element); //put element object into base array
                d.data[rowId].base = base; //set new property of object and put the array into it
                d.data[rowId]["base-many-count"] = 1; //set the many count as well                           
            } else {
                delete d.data[rowId].base;
                d.data[rowId]["base-many-count"] = 0; //set the many count as well            
            }
        }
    }
}