optionsPair documentation

ChopShop

I am trying to use a nested table based on this example https://editor.datatables.net/examples/datatables/nested.html.
However I can't seem to get it working right.
I can see "id" from the optionsPair in the data. I tried to find some documentation on this value but didn't find anything that cleared this up for me. I assume that it is the linking field for the next value up.

My data source is a restful backend.

I can't seem to get the regions to list for the country. The fields are blank.

This is what I have so far:

var region_editor = new $.fn.dataTable.Editor( {
    ajax: {
        create: {
            type: 'POST',
            url:  '/data?data_type=region',
        },
        edit: {
            type: 'PUT',
            url:  '/data/{id}?data_type=region',
        },
        remove: {
            type: 'DELETE',
            url:  '/data/{id}'
        }
    },
    fields: [
        {
            "label": "Region:",
            "name": "region"
        },
        {
            "label": "Status:",
            "name": "status",
            "type": "radio",
            "options": [
              { label: "Active", value: "Active" },
              { label: "InActive",  value: 'InActive' }
            ]
        },
    ]
} );


var country_editor = new $.fn.dataTable.Editor( {
    ajax: {
        create: {
            type: 'POST',
            url:  '/data?data_type=country&link=region',
        },
        edit: {
            type: 'PUT',
            url:  '/data/{id}?data_type=country&link=region',
        },
        remove: {
            type: 'DELETE',
            url:  '/data/{id}'
        }
    },
    table: '#countrytable',
    fields: [
        {
            "label": "Country:",
            "name": "country.country"
        },
        {
            "label": "Status:",
            "name": "country.status",
            "type": "radio",
            "options": [
              { label: "Active", value: "Active" },
              { label: "InActive",  value: 'InActive' }
            ]               
        },      
        {
            label: 'Region:',
            name: 'country.region',
            type: 'datatable',
            editor: region_editor,
            optionsPair: {
                value: 'region.id',
            },
            config: {
                ajax: {
                    create: {
                        type: 'POST',
                        url:  '/data?data_type=region',
                    },
                    edit: {
                        type: 'PUT',
                        url:  '/data/{id}?data_type=region',
                    },
                    remove: {
                        type: 'DELETE',
                        url:  '/data/{id}'
                    }
                },              
                buttons: [
                    { extend: 'create', editor: region_editor },
                    { extend: 'edit',   editor: region_editor },
                    { extend: 'remove', editor: region_editor }
                ],
                columns: [
                    {"data": "region"},
                    {"data": "status"}
                ],
                dom: bstrapDom,
            }
        }           
    ]
} );

var country_table = $('#countrytable').DataTable( {
    ajax: '/data?data_type=country&link=region',
    buttons: [
        { extend: "create", editor: country_editor },
        { extend: "edit",   editor: country_editor },
        { extend: "remove", editor: country_editor },
        'reload',
        'export'
    ] ,
    columns: [
        {"data": "country.country"},
        {"data": "country.status"},
        {"data": "region.name"}
    ],
    dom: bstrapDom,
    select: true,
    lengthChange: false
} );

My data looks something like this.:
{'data': [{'DT_RowId': 58, 'country': {'region': '', 'status': 'Active', 'country': 'Canada'}, 'region': {'name': ''}},
{'DT_RowId': 59, 'country': {'region': '49', 'status': 'Active', 'country': 'USA'}, 'region': {'name': 'North Americas'}},
{'DT_RowId': 61, 'country': {'region': '', 'status': 'Active', 'country': 'South Africa'}, 'region': {'name': ''}}],
'options': {'region': [{'label': 'North Americas', 'value': 49}, {'label': 'Asia Pacifc', 'value': 50}, {'label': 'EAMER', 'value': 51}, {'label': 'Latin America', 'value': 52}]},
'files': [], 'recordsTotal': '3', 'recordsFiltered': '3'}

rf1234

I think you only need "optionsPair" if you would like to use something different from a label - value pair as the options for your field type "datatable".

Since I never want this I don't use "optionsPair". In the example that you are quoting

}, {
        label: 'Site:',
        name: 'users.site',
        type: 'datatable',
        editor: siteEditor,
        optionsPair: {
            value: 'id',
        },
        config: {
            ajax: '../php/sitesNested.php',
            buttons: [
                { extend: 'create', editor: siteEditor },
                { extend: 'edit',   editor: siteEditor },
                { extend: 'remove', editor: siteEditor }
            ],
            columns: [
                {
                    title: 'Name',
                    data: 'name'
                },
                {
                    title: 'Continent',
                    data: 'continent'
                }
            ]
        }
    }

"optionsPair" specifies a different field to contain the values. It is not "value" but "id". I guess that is all.

Please take a look at this as well:
https://editor.datatables.net/reference/field/datatable

ChopShop

Odd that it didn't come up in Search. I see listings for General, Examples, and forum but not for reference section. Is the reference section not indexed?

rf1234

Maybe! I found this searching for "optionsPair". That took me to the "select" etc. fields that have the documentation.

ChopShop

I bit of a round about way of getting to it. I have managed with those settings to get the nested table to populate and edit correctly. Here is my code now.

    var regioneditor = new $.fn.dataTable.Editor( {
        ajax: {
            create: {
                type: 'POST',
                url:  '/data?data_type=region',
            },
            edit: {
                type: 'PUT',
                url:  '/data/{id}?data_type=region',
            },
            remove: {
                type: 'DELETE',
                url:  '/data/{id}'
            }
        },
        fields: [
            {
                "label": "Region:",
                "name": "region"
            },
            {
                "label": "Status:",
                "name": "status",
                "type": "radio",
                "options": [
                  { label: "Active", value: "Active" },
                  { label: "InActive",  value: 'InActive' }
                ]
            },
        ]
    } );    



    var country_editor = new $.fn.dataTable.Editor( {
        ajax: {
            create: {
                type: 'POST',
                url:  '/data?data_type=country&link=region',
            },
            edit: {
                type: 'PUT',
                url:  '/data/{id}?data_type=country&link=region',
            },
            remove: {
                type: 'DELETE',
                url:  '/data/{id}'
            }
        },
        
        table: '#countrytable',
        fields: [
            {
                "label": "Country:",
                "name": "country.country"
            },
            {
                "label": "Status:",
                "name": "country.status",
                "type": "radio",
                "options": [
                  { label: "Active", value: "Active" },
                  { label: "InActive",  value: 'InActive' }
                ]               
            },      
            {
                label: 'Region:',
                name: 'country.region',
                type: 'datatable',
                editor: regioneditor,
                optionsPair: {
                    value: 'region.value',
                    label: 'region.label',
                },
                config: {
                    ajax: '/data?data_type=region',             
                    buttons: [
                        { extend: 'create', editor: regioneditor },
                        { extend: 'edit',   editor: regioneditor },
                        { extend: 'remove', editor: regioneditor }
                    ],
                    columns: [
                        {"data": "region"},
                        {"data": "status"}
                    ],
                    dom: bstrapDom,
                }
            }           
        ]
    } );

    var country_table = $('#countrytable').DataTable( {
        ajax: '/data?data_type=country&link=region',
        buttons: [
            { extend: "create", editor: country_editor },
            { extend: "edit",   editor: country_editor },
            { extend: "remove", editor: country_editor },
            'reload',
            'export'
        ] ,
        columns: [
            {"data": "country.country"},
            {"data": "country.status"},
            {"data": "region.name"}
        ],
        dom: bstrapDom,
        select: true,
        lengthChange: false
    } );

But I still have one odd problem. When I try to assign a country to a region and hit the UPDATE button. It posts the data from the update with one exception. The country.region field is null. Even though I can select a region from the list it fails to pass the new value on to the ajax call.
Do you have any ideas on why it is not passing the values through?

rf1234

Sorry, can't help with that. I am only using field type 'datatable' for parent - child editing.

allan

Are you able to PM me a link to the page showing the issue please? I'm particularly interested to see what the JSON used to populate the nested table is.

            optionsPair: {
                value: 'region.value',
                label: 'region.label',
            },

The label is ignored by the datatable field type. This is the block of code for how Editor's datatable gets the value from the table:

        let rows = conf.dt
            .rows({selected: true})
            .data()
            .pluck(conf.optionsPair.value)
            .toArray();

pluck has supported nested values since DataTables 1.12 - what version of DataTables are you using?

Allan

ChopShop

I figured it out. I needed to map the fields to the data returned by the ajax query. It did not have the label, value structure.

{
                label: 'Region:',
                name: 'country.region',
                type: 'datatable',
                editor: regioneditor,
                optionsPair: {
                    value: 'DT_RowId',
                    label: 'region'
                },
                config: {
                    ajax: '/data?data_type=region',             
                    buttons: [
                        { extend: 'create', editor: regioneditor },
                        { extend: 'edit',   editor: regioneditor },
                        { extend: 'remove', editor: regioneditor }
                    ],
                    columns: [
                        {"data": "region"},
                        {"data": "status"}
                    ],
                    dom: bstrapDom,
                }

allan

Awesome - nice one

Allan