Returning more than just value and description within joined table for select

Datagaard

Is it possible to return more column data for a joined table that populates a select option control?

Currently I have leftJoin between two tables to populate a select control which has the CateringGroupID and CateringGroupDescription.

<?php

/*
 * Editor server script for DB table Catering_Items
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// 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
Editor::inst( $db, 'Catering_Items', 'CateringID' )
    ->fields(
        Field::inst( 'Catering_Items.CateringID' )->set(false),
        Field::inst( 'Catering_Items.CateringDescription' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen',50),
        Field::inst( 'Catering_Items.CateringGroupID' )
            ->options( 'CateringGroups','CateringGroupID', 'CateringGroupDescription')
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'CateringGroups.CateringGroupDescription' ),
        Field::inst( 'Catering_Items.Available' )
            ->setFormatter(function ($val, $data, $opts) {
                return ! $val ? 0 : 1;          
            })
    )
    ->leftJoin( 'CateringGroups', 'CateringGroups.CateringGroupID', '=', 'Catering_Items.CateringGroupID' )
    ->process( $_POST )
    ->json();
?>

But what I would like to do is return an additional column from the leftJoined table called "CateringGroups.Inactive", so that I can disable the select option for a specific row depending on whether that CateringGroup record is Inactive or not.

If this can be done, how would you go about disabling that select option too.

I have been able to do it in another form not using dataTables; code snippet as below:

tdV.setAttribute("style","text-align: right;"); 
var col = $meta[j]['cname'];
var select = document.createElement('select');
var bSelected = false;
var bAvailable = true;
for (a = 0; a <$assoc.length; a++){
     if ($tbl[i][col] == $assoc[a]['id']) {bSelected = true;} else {bSelected = false;}
     if ($assoc[a]['Available'] == 1){bAvailable = true;} else {bAvailable = false;}
     var option = new Option($assoc[a]['description'],$assoc[a]['id']);
     if (bSelected) {option.setAttribute('selected','selected');}
     if (!bAvailable) {option.setAttribute('disabled','disabled');}
     select.setAttribute('class','form-control');                                       
     select.appendChild(option);
}
div.appendChild(select);

allan

You can just add:

Field::inst( 'CateringGroups.Inactive' )

You can have as many of the joined table's columns as you want.

If this can be done, how would you go about disabling that select option too.

Just don't include it as a field in your Editor Javascript configuration.

Regards,
Allan

Datagaard

Hi Allan,

Okay I can include the additional field, but how do I assign it to only the select options, so that I can disable the "Inactive" Catering Group within the select control?

New php code:

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'Catering_Items', 'CateringID' )
    ->fields(
        Field::inst( 'Catering_Items.CateringID' )->set(false),
        Field::inst( 'Catering_Items.CateringDescription' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen',50),
        Field::inst( 'Catering_Items.CateringGroupID' )
            ->options( 'CateringGroups','CateringGroupID', 'CateringGroupDescription')
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'CateringGroups.CateringGroupDescription' ),
        **Field::inst( 'CateringGroups.Available')**,
        Field::inst( 'Catering_Items.Available' )
            ->setFormatter(function ($val, $data, $opts) {
                return ! $val ? 0 : 1;          
            })
    )
    ->leftJoin( 'CateringGroups', 'CateringGroups.CateringGroupID', '=', 'Catering_Items.CateringGroupID' )
    ->process( $_POST )
    ->json();

What do I add in the js Editor code?

{
    "label": "Catering Group ID:",
    "name": "Catering_Items.CateringGroupID",
    "type": "select"
},

allan

I'm afraid I'm not quite understanding the question, but I think that's because I don't know what your data values are.

Are you saying you have a list of options, and you want one of them not to be selectable (i.e. disabled), while others in the list are selectable? I'm afraid that is not something that Editor's select list currently provides an option for built in.

Having said that, if that is required, then you could use field().input() to get the select element and then use jQuery to disable that specific option by just selecting it and adding the disabled attribute.

Allan

Datagaard

Hi Allan,

In response to your question:

_Are you saying you have a list of options, and you want one of them not to be selectable (i.e. disabled), while others in the list are selectable? _

Yes, that is exactly what I am after.

So will try your suggestion.

Thanks

Datagaard

Hi Allan,

I am not sure where to add the necessary code for the field().input?

Is it to the js file in the editor?

If I have added the Field::inst('CateringGroups.Available') to the Editor in php, should I not see this in the list of fields being returned in the editor? As I don't see it currently there!

See attached screen shot

I have been able to do something within the table as far as displaying the data depending on the availability of the catering group but not sure how to set it up in the editor part.

var table = $('#Catering_Items').DataTable( {
        dom: 'Bfrtip',
        ajax: 'php/table.Catering_Items.php',
        columns: [
            { "data": "Catering_Items.CateringID" },
            { "data": "Catering_Items.CateringDescription" },
            { 
                "data": "CateringGroups.CateringGroupDescription",
                "render": function (data, type, row){
                    if (row.CateringGroups.Available == 0){
                        return data + '(Unavailable)';
                    }
                    else{
                        return data;
                    }
                }
            },

allan

You'd need to listen for the init event and perform the update there.

table.on( 'init.dt', function () {
  var select = editor.field( 'myField' ).input();
} );

Then find the option you want from inside the select element and disable it.

Allan

Datagaard

Hi Allan,

Thanks for your help and patients as I am a new be to all this.

I have it working now, although I am not sure whether this is the best approach. Would be grateful is you could comment on the code below:

$('#Catering_Items').on( 'init.dt', function(e, settings, json) {
    var select = editor.field( 'Catering_Items.CateringGroupID').input();
    var jData = json.data;
    var grpName = '';
    var grpIsAvail = 0;
    var disabledGrps = [];
    for(i = 0; i < jData.length; i++){
        grpID = jData[i]['Catering_Items'].CateringGroupID;
        grpIsAvail = jData[i]['CateringGroups'].Available;
        if (grpIsAvail == 0){
                disabledGrps.push(grpID);       
        }
    }
    
    var grpOpt = '';
    for(j = 0; j < select[0].length; j++){
        grpOpt = select[0][j];
        for (k = 0; k < disabledGrps.length; k++){
                if (grpOpt.value == disabledGrps[k]){
                    grpOpt.setAttribute('disabled','disabled');
                    break;
                }           
        }
    }
   });

allan

That looks good to me. It could probably be reduced in code size (using jQuery to select the required values in the select list for example), but I think what you have will be performant and work nicely.

Allan

Datagaard

Sorry Allan,

I am a relative new be to all this. I added the code

$('#Catering_Items').on( 'init.dt', function() {
    var select = editor.field( 'CateringGroups.Available').input();
   });

But get editor undefined

Also tried

table.on( 'init.dt', function() {
    var select = editor.field( 'CateringGroups.Available').input();
   }); 

Datagaard

HI Allan,

Thanks for your help and patients. I am a new be to all this. I have tried the following code:

table.on( 'init.dt', function() {
    var select = editor.field( 'CateringGroups.Available').input();
   }); 

AND

$('#Catering_Items').on( 'init.dt', function() {
    var select = editor.field( 'CateringGroups.Available').input();
   });

But get editor undefined error