Node.js - Where clause stopped working after updating Knex

grapefruitmoon

I've got an node.js app in development using Editor and DataTables, that I've just gotten back to after a bit of a break. I updated all the dependencies, and after fixing a few minor things, I'm having a big problem when using the Where clause functionality in Editor. Every time its called I get the following error:

Unhandled promise error: [object Promise]TypeError: The operator "undefined" is not permitted app.js:29 stack: TypeError: The operator "undefined" is not permitted at MSSQL_Formatter.operator (/Dev/members-app/node_modules/knex/lib/formatter.js:203:13)

This was working fine a few months back. Taking the Where clause out, everything works fine. I've tried forcing in the operator to no avail. I'm developing on Mac with VS Code, database is Azure SQL Database.

Any clues??

colin

It sounds like it's forming the query wrong. Please can you add debug to the server-side script so we can see the query that's being executed. This thread explains how to add debug.

Colin

grapefruitmoon

Thanks Colin. I've tried a few variations of this, but I may not be using the debug method correctly. When I have it set (without a where clause to test it), the data returns ok in the Json, but the debug element is empty:

"debug":[]}

allan

Yes, unfortunately the linked thread doesn't actually discuss the NodeJS debugging - it is a little different from the PHP and .NET libraries we provide since the NodeJS libraries use Knex for the database abstraction. You need to add debug: true to your Knex database configuration object. The SQL statements will then be dumped onto the node console.

Can you show me the Editor NodeJS code you are using please?

Thanks,
Allan

grapefruitmoon

Ah, thanks Allan, that did the trick. This will make a massive difference!

So the issue with the Where clause didn't get as far as submitting any SQL, but the debug flag did generate a few clues. I move the Where into a seperate function call after the Editor initialisation, and now it is working again. Not sure why it's changed, but as long as it works, I'm happy.

Thank you both so much for your input!

allan

interesting. We'll keep an ear out for similar issues - but good to hear you've got it working .

Allan

KevinSpirit

Hi. I am also experiencing this issue with Editor 1.19.4 and Knex 0.21.1 using mariadb 10.4.13. When adding a where clause to a leftJoin(), the following error is generated:

Unhandled promise error:  [object Promise]TypeError: The operator "undefined" is not permitted
stack: TypeError: The operator "undefined" is not permitted
    at Formatter.operator (/home/admin/app/node_modules/knex/lib/formatter.js:203:13)
    at QueryCompiler_MySQL.whereBasic (/home/admin/app/node_modules/knex/lib/query/compiler.js:665:22)
    at QueryCompiler_MySQL.where (/home/admin/app/node_modules/knex/lib/query/compiler.js:383:34)
    at components.map (/home/admin/app/node_modules/knex/lib/query/compiler.js:116:69)
    at Array.map (<anonymous>)
    at QueryCompiler_MySQL.select (/home/admin/app/node_modules/knex/lib/query/compiler.js:116:35)
    at QueryCompiler_MySQL.toSQL (/home/admin/app/node_modules/knex/lib/query/compiler.js:62:29)
    at Builder.toSQL (/home/admin/app/node_modules/knex/lib/query/builder.js:72:44)
    at /home/admin/app/node_modules/knex/lib/runner.js:31:36
    at client.acquireConnection.catch.then (/home/admin/app/node_modules/knex/lib/runner.js:260:24)

My Editor definition in Node.js is as follows:

  let editor = new Editor(knex, 'recordings', 'filename')
    .fields(
      new Field('recordings.filename'),
      new Field('recordings.transcript'),
      new Field('labels.user'),
      new Field('labels.accurate'),
      new Field('labels.toxic'),
      new Field('labels.comment')
    )
    .leftJoin('labels', 'labels.filename', '=', 'recordings.filename')
    .where('labels.user', 'XXXX');

I have debug turned on in Knex, but it is not getting as far as generating a SQL statement.

Thanks in advance for any help!

Kevin

allan

Hi Kevin,

Interesting - could you try this please:

.where({'labels.user': 'XXXX'});

i.e. as an object rather than two parameters?

Thanks,
Allan

grapefruitmoon

Hi Kevin,

When I had the issue, I had to move the Where clause out of the Editor initialisation. So after the let editor =... statement, I have added:

        editor.where( function () {
            this.where( 'Member.BranchID', vbranch );
        } );

KevinSpirit

Hi Allan,

Changing to the object rather than the 2 parameters has fixed it - working ok. Thanks very much!

Thanks also to @grapefruitmoon for the tip.

Kevin