How do i make a datatables editor view show the lookup labels instead of values

defdog

for example

    var editor = new $.fn.dataTable.Editor( {
        ajax: 'js/DataTablesEditor/php/table.trailer.php',
        table: '#trailer',
        fields: [

            {   
                "label": "ID",
                "name": "id_trailer",
                "type": "readonly"

            },

            {   
                "label": "Trailer #",
                "name": "number"
            },

            {   
                "label": "Type",
                "name": "type",
                "type": "select",
                "options": [
                    { label: 'van', value: 'van' },
                    { label: 'reefer', value: 'reefer' },
                    { label: 'flatbed', value: 'flatbed' },
                    { label: 'container', value: 'container' },
                    { label: 'rail', value: 'rail' }
                    // etc
                    ]
            },

            {
                "label": "Length",
                "name": "size"
            },

            {
                "label": "Carrier",
                "name": "id_carrier"
            },

            {
                "label": "Status",
                "name": "id_trailer_status"
            },

        ]
    } );

    var table = $('#trailer').DataTable( {
        dom: 'Bfrtip',
        ajax: 'js/DataTablesEditor/php/table.trailer.php',
        columnDefs: [
                { targets: [ 0 ], visible:false },
                { targets: [ 5, 8 ], render:function ( data, type, row, meta ) {
                    return ((data=='1')?'<img src="img/greencheck.png" width="15" alt="1" />':'<img src="img/redx.png" width="15" alt="0" />');
                    } },
                { targets: [ 2,4,6,7 ], render:function ( data, type, row, meta ) {
                    console.log(data,type,row,meta);
                    if (false == true) alldd.ddval({name: 'carrier',type:'val',ref:$(this),arg:data});
                    return (data);
                    } },
            ],  
        columns: [

            { "data": "id_trailer" }, 
            { "data": "number" },
            { "data": "type" },
            { "data": "size" },
            { "data": "id_carrier" },
            { "data": "id_trailer_status" },
            { "data": "trailer_owned" },
            { "data": "id_condition" },
            { "data": "in_yard_status" },
         
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    } );

How do i show the label for type (column 3) and the label for id_carrier (5) . Also how do i get the values for the editor for a select box (they are in separate tables)?

allan

Hi,

You need to use a leftJoin in the server-side script. Have a read through the documentation for this and let me know if you have any questions about it.

Allan

defdog

That was super helpful - i have it displaying the values - what I want now is to be able to do inline edit. Edit will use a select.

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

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'js/DataTablesEditor/php/table.trailer.php',
        table: '#trailer',
        fields: [
        
            {
                "label": "ID",
                "name": "trailer.id_trailer",
                "type": "readonly"

            },
        
            {
                "label": "Trailer #",
                "name": "trailer.number"
            },
        
            {
                "label": "Type",
                "name": "trailer.type"
            },
        
            {
                "label": "Length",
                "name": "trailer.size"
            },
        
            {
                "label": "Carrier",
                "name": "trailer.id_carrier"
            },
        
            {
                "label": "Status",
                "name": "trailer.id_trailer_status"
            },
        
            {
                "label": "Owned",
                "name": "trailer.trailer_owned"
            },
        
            {
                "label": "Condition",
                "name": "id_condition"
            },
        
            {
                "label": "In Yard",
                "name": "trailer.in_yard_status"

            },
        
        ]
    } );


    var table = $('#trailer').DataTable( {
        dom: 'Bfrtip',
        ajax: 'js/DataTablesEditor/php/table.trailer.php',
        columnDefs: [
                { targets: [ 0 ], visible:false },
                { targets: [ 5, 8 ], render:function ( data, type, row, meta ) {
                    return ((data=='1')?'<img src="img/greencheck.png" width="15" alt="1" />':'<img src="img/redx.png" width="15" alt="0" />');
                    } },
                /* { targets: [ 2,4,6,7 ], render:function ( data, type, row, meta ) {
                    console.log(data,type,row,meta);
                    if (false == true) alldd.ddval({name: 'carrier',type:'val',ref:$(this),arg:data});
                    return (data);
                    } }, */
            ],
        columns: [
            { "data": "trailer.id_trailer" }, 
            { "data": "trailer.number" },
            { "data": "trailer_type.trailer_type" },
            { "data": "trailer.size" },
            { "data": "carrier.name" },
            { "data": "trailer_status.trailer_status" },
            { "data": "trailer_owner.owner" },
            { "data": "trailer.id_condition" },
            { "data": "trailer.in_yard_status" },
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    } );

    $('#trailer').on( 'click', 'tbody td:not(:first-child)', function (e) {
        console.log('yup');
        editor.inline( this, {
            submit: 'changed',
            submitOnBlur: 'true'
        } );
    } );
});

}(jQuery));

php code is:

Editor::inst( $db, 'trailer', 'id_trailer' )
    ->fields(Field::inst( 'trailer.id_trailer' )
            ,Field::inst( 'trailer.number' )
            ,Field::inst( 'trailer.type' )->options( Options::inst()->table( 'trailer_type' )->value( 'trailer_type.trailer_type' )->label('trailer_type.trailer_type'))
            ,Field::inst( 'trailer_type.trailer_type')
            ,Field::inst( 'trailer.size' )
            ,Field::inst( 'trailer.id_carrier' )->options( Options::inst()->table( 'carrier' )->value( 'id_carrier' )->label('name'))
            ,Field::inst( 'carrier.name')
            ,Field::inst( 'trailer.id_trailer_status' )->options( Options::inst()->table( 'trailer_status' )->value( 'id_trailer_status' )->label('trailer_status'))
            ,Field::inst( 'trailer_status.trailer_status')
            ,Field::inst( 'trailer.trailer_owned' )->options( Options::inst()->table( 'trailer_owner' )->value( 'owned_code' )->label('owner'))
            ,Field::inst( 'trailer_owner.owner')
            ,Field::inst( 'trailer.id_condition' )->options( Options::inst()->table( 'trailer_condition' )->value( 'id_condition' )->label('trailer_condition'))
            ,Field::inst( 'trailer_condition.trailer_condition')
            )
    ->leftJoin( 'trailer_type', 'trailer_type.trailer_type', '=', 'trailer.type' )
    ->leftJoin( 'carrier', 'carrier.id_carrier', '=', 'trailer.id_carrier' )
    ->leftJoin( 'trailer_status', 'trailer_status.id_trailer_status', '=', 'trailer.id_trailer_status' )    ->leftJoin( 'trailer_owner', 'trailer_owner.owned_code', '=', 'trailer.trailer_owned' )
    ->leftJoin( 'trailer_condition', 'trailer_condition.id_condition', '=', 'trailer.id_condition' )
    ->process( $_POST )
    ->json();

allan

This example shows how you can do an inline edit with a joined table and a select input.

The key is to use columns.editField to tell Editor which field it should edit for the given column.

Allan

defdog

Thanks! I got it working!