Editor join array fails with server side processing.

Editor join array fails with server side processing.

naspersgaspnaspersgasp Posts: 53Questions: 14Answers: 1

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

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

    Thanks,
    Allan

  • naspersgaspnaspersgasp Posts: 53Questions: 14Answers: 1

    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.

  • naspersgaspnaspersgasp Posts: 53Questions: 14Answers: 1

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

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

    Regards,
    Allan

  • naspersgaspnaspersgasp Posts: 53Questions: 14Answers: 1

    Hi,

    You are legend! Fixed. Thanks.

    Regards.

This discussion has been closed.