Editor join array fails with server side processing.

naspersgasp

Hi,

Good day.

Looking at using a select dropdown. I had a look at the joinArray example. Seemed straight forward. Unfortunately it does not work with server side processing. The error I get:

Unhandled promise error:  [object Promise]Error: select count(`users`.`id`) as `cnt` from `users` as `0` left join `sites` on `sites`.`id` = `users`.`site` - ER_BAD_FIELD_ERROR: Unknown column 'users.id' in 'field list'
stack: Error: ER_BAD_FIELD_ERROR: Unknown column 'users.id' in 'field list'
    at Query.Sequence._packetToError (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Query.ErrorPacket (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    at Parser.write (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/protocol/Parser.js:76:12)
    at Protocol.write (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/Connection.js:103:28)
    at Socket.emit (events.js:180:13)
    at addChunk (_stream_readable.js:274:12)
    at readableAddChunk (_stream_readable.js:261:11)
    at Socket.Readable.push (_stream_readable.js:218:10)
    at TCP.onread (net.js:581:20)
    --------------------
    at Protocol._enqueue (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/protocol/Protocol.js:145:48)
    at Connection.query (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/mysql/lib/Connection.js:208:25)
    at /Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/knex/lib/dialects/mysql/index.js:161:18
    at Promise._execute (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/debuggability.js:303:9)
    at Promise._resolveFromExecutor (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/promise.js:483:18)
    at new Promise (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/promise.js:79:10)
    at Client_MySQL._query (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/knex/lib/dialects/mysql/index.js:155:12)
    at Client_MySQL.query (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/knex/lib/client.js:206:17)
    at Runner.<anonymous> (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/knex/lib/runner.js:155:36)
    at Runner.tryCatcher (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/util.js:16:23)
    at Runner.query (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/method.js:15:34)
    at /Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/knex/lib/runner.js:61:21
    at tryCatcher (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/util.js:16:23)
    at /Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/using.js:185:26
    at tryCatcher (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/thejj/Downloads/Editor-NodeJS-1.7.4/node_modules/bluebird/js/release/promise.js:512:31)

I'll see if I can find the issue, but maybe you can find it much quicker Thanks.

Regards.

allan

Hi,

Can you show me both your client-side and server-side code for Editor please?

Thanks,
Allan

naspersgasp

Hi,

I'm using the example code so you can test it easily. It is the controllers/joinArray.js and examples/joinArray.html in the Editor-NodeJS-1.7.4 download.

All I did was add the server side option to the joinArray.html file where the table is initialised. Unfortunately I can't seem to find the issue, but what I do know is the SQL bit is incorrect where it says "users as 0"?

select count(`users`.`id`) as `cnt` from `users` as `0` left join `sites` on `sites`.`id` = `users`.`site`

should be

select count(`users`.`id`) as `cnt` from `users` as `users` left join `sites` on `sites`.`id` = `users`.`site`

I don't have a debugger setup to step through the nodejs code. I'm sure it is something simple. Thanks.

Regards.

naspersgasp

Hi,

Good day.

joinArray.js:

let db = require('../db');
let router = require('express').Router();
let {
    Editor,
    Field,
    Validate,
    Format,
    Options,
    Mjoin
} = require("datatables.net-editor-server");

router.all('/api/joinArray', async function(req, res) {
    let editor = new Editor(db, 'users')
        .fields(
            new Field('users.first_name'),
            new Field('users.last_name'),
            new Field('users.site').options(
                new Options().table('sites').value('id').label('name')
            ),
            new Field('sites.name')
        )
        .leftJoin('sites', 'sites.id', '=', 'users.site')
        .join(
            new Mjoin('permission')
                .link('users.id', 'user_permission.user_id')
                .link('permission.id', 'user_permission.permission_id')
                .order('name asc')
                .fields(
                    new Field('id')
                        .validator(Validate.required())
                        .options(
                            new Options().table('permission').value('id').label('name')
                        ),
                    new Field('name')
                )
        );

    await editor.process(req.body);
    res.json(editor.data());
});

module.exports = router;

joinArray.html:

<script type="text/javascript" language="javascript" class="init">
var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "/api/joinArray",
        table: "#example",
        fields: [ {
                label: "First name:",
                name:  "users.first_name"
            }, {
                label: "Last name:",
                name:  "users.last_name"
            }, {
                label: "Site:",
                name:  "users.site",
                type:  "select"
            }, {
                "label": "Permissions:",
                "name": "permission[].id",
                "type": "checkbox"
            }
        ]
    } );

    $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "/api/joinArray",
            type: 'POST'
        },
        columns: [
            { data: "users.first_name" },
            { data: "users.last_name" },
            { data: "sites.name" },
            { data: "permission", render: "[, ].name" }
        ],
        select: true,
        serverSide: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );
</script>
            <table id="example" class="display" cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th>First name</th>
                        <th>Last name</th>
                        <th>Location</th>
                        <th>Permissions</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>First name</th>
                        <th>Last name</th>
                        <th>Location</th>
                        <th>Permissions</th>
                    </tr>
                </tfoot>
            </table>

I've only included the important bits of joinArray.html. The only difference compared to the example is the server side setting is true.

Regards.

allan

Awesome! Thanks for pointing me in the right direction. I've committed the fix.

Regards,
Allan

naspersgasp

Hi,

You are legend! Fixed. Thanks.

Regards.