How make a join ? alias error

e.jourde

Hello I have two linked tables, actually a table whose label is translated.
The structure is simple:
table country: id_country, id_translate, alpha2, alpha3, activate
translate_language table: id_translate, id_language, translation.

this wording must be able to be modified

I made a leftjoin which displayed very well but did not update the linked table.
I went through a join and I have error messages, I don't understand where my error is.

here is the javascript:

var editor_country = new $.fn.dataTable.Editor( {
                    ajax: 'php/table.country.php?langue=48',
                    table: '#country',
                    fields: [
                        {
                            
                            "name": "c_translate",type: "hidden"
                        },
                        {
                            
                            "name": "tl_translate",type: "hidden"
                        },
                        {
                            "label": "id_language :",
                            "name": "translate_language.id_language",type: "hidden"
                        },
                        {
                            "label": "Libellé :",
                            "name": "translation"
                        },
                        {
                            "label": "Alpha2 :",
                            "name": "country.alpha2"
                        },
                        {
                            "label": "Alpha3 :",
                            "name": "country.alpha3"
                        },
                        {
                            "label": "Actif :",
                            "name": "country.activate",
                            "type": "checkbox"
                            
                            
                        }
                    ]
                } );
                $('#country').on( 'click', 'tbody td', function (e) {
                    editor_country.inline( this, {
                        submit: 'allIfChanged'
                    } );
                } );
                var table = $('#country').DataTable( {
                    dom: 'Bfrtip',
                    ajax: 'php/table.country.php?langue=48',
                    columns: [
                        
                            { data: "translate_language.translation", title:"Libellé" }
                        ,
                        {
                            "data": "country.alpha2",title:"Alpha 2"
                        },
                        {
                            "data": "country.alpha3",title:"Alpha3"
                        },
                        {
                            "data": "country.activate",title:"Actif"
                        }
                    ],
                    "scrollY":        "65vh",
                    "scrollCollapse": true,
                    "paging":         false,

                    keys: {
                        editor:  editor_country
                    },
                    select: true,
                    lengthChange: false,
                    autoFill: {
                        editor:  editor_country
                    },
                    select: {
                        style:    'os',
                        selector: 'td:first-child',
                        blurable: true
                    },
                    buttons: [
                        { extend: 'create', editor: editor_country },
                        { extend: 'edit',   editor: editor_country },
                        { extend: 'remove', editor: editor_country }
                    ]
                } );
            }

and php :

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables \ Editor \ Join, 
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'country', 'id_country' )
->fields(
    Field::inst( 'country.id_translate as c_translate')->set(false),
        Field::inst( 'country.alpha2' )
        ->validator( Validate::maxLen( 2 ) ),
        Field::inst( 'country.alpha3' )
        ->validator( Validate::maxLen( 3 ) ),
        Field::inst( 'country.activate' )  
        ->setFormatter( function ( $val, $data, $opts ) {
            return ! $val ? 0 : 1;
        } )
    )
    -> join (
        Join :: inst ('translate_language', 'object')
        -> link('country.id_translate','translate_language.id_translate')
        -> where('translate_language.id_language',$_GET['langue'],"=")
        -> fields (
            Field::inst( 'translate_language.id_translate as tl_translate' )->set(false),
            Field::inst( 'translate_language.id_language' )->set(false),
            Field::inst( 'translate_language.translation' )
            ) 
        )
    
    ->debug( true )
    ->process( $_POST )

    ->json();
...

//error message

DataTables warning: table id=country - Table selected fields (i.e. '{table}.{column}') in Join must have a name alias which does not contain a period ('.'). Use name('---') to set a name for the field

i tried replace link by -> link('c_translate','tl_translate') but it provocate error Editor\Join.php on line 270.

sorry for my very bad english. i begin with datatable and editor and i not understand what is this problème with alias.

thanks.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Triskell2k1

Try to rename fields without "."
"name": "country.alpha2" --> "name": "country_alpha2" for example

e.jourde

All the fields are aliased now. i have not error, i see all lines but update of field linked (translation) make nothing.

in debug i see update request for table country but not for the table translate_language.

php :
Editor::inst( $db, 'country', 'id_country' )
->fields(
Field::inst( 'country.id_translate')->set(false),
Field::inst( 'country.alpha2 as country_alpha2' )
->validator( Validate::maxLen( 2 ) ),
Field::inst( 'country.alpha3 as country_alpha3' )
->validator( Validate::maxLen( 3 ) ),
Field::inst( 'country.activate as country_activate' )
->setFormatter( function ( $val, $data, $opts ) {
return ! $val ? 0 : 1;
} ),
Field::inst( 'translate_language.translation as translate_language_translation' )

)
->leftJoin("translate_language", "translate_language.id_translate=country.id_translate and translate_language.id_language= ".$_GET['langue'],"","")
-> join (
    Join :: inst ('translate_language', 'object')
    -> link('country.id_translate','translate_language.id_translate')

    -> where('translate_language.id_language',$_GET['langue'],"=")
    -> fields (
        Field::inst( 'translate_language.id_translate as translate_language_id_translate' )->set(false),
        Field::inst( 'translate_language.id_language as translate_language_id_language' )->set(false)
        ) 
    )

->debug( true )
->process( $_POST )

and debug :

{"debug":[{"query":"SELECT country.id_country as \"country.id_country\" FROM country WHERE country.id_country = :where_0 ","bindings":[{"name":":where_0","value":"4","type":null}]},{"query":"UPDATE country SET alpha2 = :alpha2, alpha3 = :alpha3 WHERE country.id_country = :where_0 ","bindings":[{"name":":alpha2","value":"AF","type":null},{"name":":alpha3","value":"AFG","type":null},{"name":":where_0","value":"4","type":null}]},{"query":"SELECT country.id_country as \"country.id_country\", country.id_translate as \"country.id_translate\", country.alpha2 as \"country.alpha2\", country.alpha3 as \"country.alpha3\", country.activate as \"country.activate\", translate_language.translation as \"translate_language.translation\" FROM country LEFT JOIN translate_language ON translate_language.id_translate=country.id_translate and translate_language.id_language= 48 WHERE country.id_country = :where_0 ","bindings":[{"name":":where_0","value":"4","type":null}]},{"query":"SELECT DISTINCT country.id_translate as \"dteditor_pkey\", translate_language.id_translate as \"translate_language.id_translate\", translate_language.id_language as \"translate_language.id_language\" FROM country as country JOIN translate_language ON translate_language.id_translate = country.id_translate WHERE translate_language.id_language = :where_0 AND country.id_translate IN (:wherein1) ","bindings":[{"name":":where_0","value":"48","type":null},{"name":":wherein1","value":190,"type":null}]}]}

e.jourde

and if i remove leftjoin i have error :
DataTables warning: table id=country - An SQL error occurred: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "translate_language"
LINE 1: ....alpha3", country.activate as "country.activate", translate_...

allan

The way I'd probably approach this myself is to do a leftJoin per item to be translated. Forego the join method, and use an aliased leftJoin. With the translate_language table aliased once per item to look up, you'll be able to reference each individually.

Allan

e.jourde

Thanks, my solution is with setFormatter and validator.