Node.js datatables editor - Complex left join not working in node.js

Capamania

I'm using the node.js datatable editor library. When trying to do a complex left join 'portfolios_isin_mm.isin AND (securities.mic = portfolios_isin_mm.mic)' ... like this:

    let editor = new Editor( db, 'portfolios_isin_mm' )
        .fields(
            new Field( 'portfolios_isin_mm.account_id' ),
            new Field( 'portfolios_isin_mm.user_id' ),
            new Field( 'portfolios_isin_mm.uid_foreign' )
            new Field( 'securities.issuer_name' ),
            new Field( 'portfolios_isin_mm.isin' ),
            new Field( 'portfolios_isin_mm.mic' ),
           )

        .leftJoin( 'portfolios_isin', 'portfolios_isin.id', '=', 'portfolios_isin_mm.uid_foreign' )
        .leftJoin( 'securities', 'securities.isin', '=', 'portfolios_isin_mm.isin AND (securities.mic = portfolios_isin_mm.mic)' );
        //.leftJoin( 'securities', 'securities.isin', '=', 'portfolios_isin_mm.isin' );

I'm getting an error. Debugging the error shows me:

    { Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`mic)`' at line 1
        at Query.Sequence._packetToError (/home/myproject/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
        at Query.ErrorPacket (/home/myproject/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
        at Protocol._parsePacket (/home/myproject/node_modules/mysql/lib/protocol/Protocol.js:278:23)
        at Parser.write (/home/myproject/node_modules/mysql/lib/protocol/Parser.js:76:12)
        at Protocol.write (/home/myproject/node_modules/mysql/lib/protocol/Protocol.js:38:16)
        at Socket.<anonymous> (/home/myproject/node_modules/mysql/lib/Connection.js:91:28)
        at Socket.<anonymous> (/home/myproject/node_modules/mysql/lib/Connection.js:502:10)
        at Socket.emit (events.js:182:13)
        at addChunk (_stream_readable.js:283:12)
        at readableAddChunk (_stream_readable.js:264:11)
        at Socket.Readable.push (_stream_readable.js:219:10)
        at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
        --------------------
        at Protocol._enqueue (/home/myproject/node_modules/mysql/lib/protocol/Protocol.js:144:48)
        at Connection.query (/home/myproject/node_modules/mysql/lib/Connection.js:200:25)
        at /home/myproject/node_modules/knex/lib/dialects/mysql/index.js:152:18
        at Promise._execute (/home/myproject/node_modules/bluebird/js/release/debuggability.js:313:9)
        at Promise._resolveFromExecutor (/home/myproject/node_modules/bluebird/js/release/promise.js:483:18)
        at new Promise (/home/myproject/node_modules/bluebird/js/release/promise.js:79:10)
        at Client_MySQL._query (/home/myproject/node_modules/knex/lib/dialects/mysql/index.js:146:12)
        at Client_MySQL.query (/home/myproject/node_modules/knex/lib/client.js:197:17)
        at Runner.<anonymous> (/home/myproject/node_modules/knex/lib/runner.js:146:36)
        at Runner.tryCatcher (/home/myproject/node_modules/bluebird/js/release/util.js:16:23)
        at Runner.query (/home/myproject/node_modules/bluebird/js/release/method.js:15:34)
        at /home/myproject/node_modules/knex/lib/runner.js:65:21
        at tryCatcher (/home/myproject/node_modules/bluebird/js/release/util.js:16:23)
        at /home/myproject/node_modules/bluebird/js/release/using.js:185:26
        at tryCatcher (/home/myproject/node_modules/bluebird/js/release/util.js:16:23)
        at Promise._settlePromiseFromHandler (/home/myproject/node_modules/bluebird/js/release/promise.js:512:31)
      code: 'ER_PARSE_ERROR',
      errno: 1064,
      sqlMessage:
       'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'.`mic)`\' at line 1',
      sqlState: '42000',
      index: 0,
      sql:
       'select count(`portfolios_isin_mm`.`id`) as `cnt` from `portfolios_isin_mm` left join `portfolios_isin` on `portfolios_isin`.`id` = `portfolios_isin_mm`.`uid_foreign` left join `securities` on `securities`.`isin` = `portfolios_isin_mm`.`isin AND (securities`.`mic = portfolios_isin_mm`.`mic)`' }

... with the php libraries it was working just fine:

    ->leftJoin( 'securities', 'securities.isin', '=', 'portfolios_isin_mm.isin AND (securities.mic = portfolios_isin_mm.mic)' ) 

How can I get the complex left join to work in node.js as well?

... I'm using $ node -v ... v10.15.0 and "datatables.net-editor-server": "^1.8.1"

allan

The only way to do this at the moment is to create a VIEW that composes that join then you can select from that view. The readTable method can be used to read information from the view while still updating to the host table.

Allan

Capamania

Thanks. Can you provide a working example in your docs?