Nested example in a specific data base schema

UMR5558

Hello everybody
I want to use datatable type field. So i try nested example in a public data base schema. All is working good. But I would like to use this field type in an another database schema. So i try the same nested example in a new database schema. I modify the code for using my nex schema (magasin in my case) when i want to update in the edit entry form i have this error:
"error: "An SQL error occurred: SQLSTATE[22P02]: Invalid text representation: 7 ERREUR: syntaxe en entrée invalide pour l'entier : « »"
In Chrome debugger i can see that there is no value post for [magasin][user][site] data form

Can you help me to find a solution.
thanks for your help.
Lionel

Link to test case: I can't give a link my server is not open
Debugger code (debug.datatables.net):
Error messages shown: "error: "An SQL error occurred: SQLSTATE[22P02]: Invalid text representation: 7 ERREUR: syntaxe en entrée invalide pour l'entier : « »"
Description of problem: In Chrome debugger i can see that there is no value post for [magasin][user][site] data form.

here are the code with my new database schema : 'magasin'
nested.html:

    var siteEditor = new $.fn.dataTable.Editor( {
        ajax: '../../controllers/sitesNested.php',
        fields: [ {
                label: 'City:',
                name: 'magasin.sites.name'
            }, {
                label: 'Continent:',
                name: 'magasin.sites.continent',
                type: 'datatable',
                options: [
                    { label: 'Africa', value: 'Africa' },
                    { label: 'Asia', value: 'Asia' },
                    { label: 'Australia', value: 'Australia' },
                    { label: 'Europe', value: 'Europe' },
                    { label: 'North America', value: 'North America' },
                    { label: 'South America', value: 'South America' }
                ]
            }
        ]
    } );
    editor = new $.fn.dataTable.Editor( {
        ajax: '../../controllers/joinNested.php',
        table: '#example',
        fields: [ {
                label: 'First name:',
                name: 'magasin.users.first_name'
            }, {
                label: 'Last name:',
                name: 'magasin.users.last_name'
            }, {
                label: 'Phone #:',
                name: 'magasin.users.phone'
            }, {
                label: 'Site:',
                name: 'magasin.users.site',
                type: 'datatable',
                editor: siteEditor,
                optionsPair: {
                    value: 'magasin.sites.id',
                },
                config: {
                    ajax: '../../controllers/sitesNested.php',
                    buttons: [
                        { extend: 'create', editor: siteEditor },
                        { extend: 'edit',   editor: siteEditor },
                        { extend: 'remove', editor: siteEditor }
                    ],
                    columns: [
                        {
                            title: 'Name',
                            data: 'magasin.sites.name'
                        },
                        {
                            title: 'Continent',
                            data: 'magasin.sites.continent'
                        }
                    ]
                }
            }
        ]
    } );

    $('#example').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: '../../controllers/joinNested.php',
            type: 'POST'
        },
        columns: [
            { data: 'magasin.users.first_name' },
            { data: 'magasin.users.last_name' },
            { data: 'magasin.users.phone' },
            { data: 'magasin.sites.name' }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    } );
} );

joinNested.php:

Editor::inst( $db, 'magasin.users' )
    ->field( 
        Field::inst( 'magasin.users.first_name' ),
        Field::inst( 'magasin.users.last_name' ),
        Field::inst( 'magasin.users.phone' ),
        Field::inst( 'magasin.users.site' )
            ->validator( Validate::dbValues(null, 'magasin.sites.id', 'magasin.sites', $db) ),
        Field::inst( 'magasin.sites.name' )
    )
    ->leftJoin( 'magasin.sites', 'magasin.sites.id', '=', 'magasin.users.site' )
    ->process($_POST)
    ->json();

sitesNested.php:
Editor::inst( $db, 'magasin.sites' )
    ->fields(
        Field::inst( 'magasin.sites.id' )->set( false ),
        Field::inst( 'magasin.sites.name' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'magasin.sites.continent' )->validator( 'Validate::notEmpty' )
    )
    ->process( $_POST )
    ->json();

allan

Hi,

Can you add ->debug(true) just before the ->process(...) call and then use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

UMR5558

Hello Allan.
Thanks a lot for your help.
I add ->debug(true) in the joinNested.php file.
In the debugger i can read
[
{
"query": "SELECT \"magasin\".\"users\".\"id\" as \"magasin.users.id\" FROM \"magasin\".\"users\" WHERE \"magasin\".\"users\".\"id\" = :where_0 ",
"bindings": [
{
"name": ":where_0",
"value": "3",
"type": null
}
]
},
{
"query": "UPDATE \"magasin\".\"users\" SET \"first_name\" = :first_name, \"last_name\" = :last_name, \"phone\" = :phone, \"site\" = :site WHERE \"magasin\".\"users\".\"id\" = :where_0 ",
"bindings": [
{
"name": ":first_name",
"value": "aurelie",
"type": null
},
{
"name": ":last_name",
"value": "siberchicot",
"type": null
},
{
"name": ":phone",
"value": "24",
"type": null
},
{
"name": ":site",
"value": "",
"type": null
},
{
"name": ":where_0",
"value": "3",
"type": null
}
]
}
]

But by chance i find a solution. Everywhere where columns are only defined by their name, I deleted the schema_name.table_name before the column name. I am surprised because in simple examples i need to use schema_name.table_name.column_name.

I will try now in my code if it's working. After i must find a solution to add options in siteEditor var by requesting my data base. But step by step.

Thanks for all.
Lionel