Node.js - Where clause stopped working after updating Knex

Node.js - Where clause stopped working after updating Knex

grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

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??

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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":[]}

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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!

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

    Allan

  • KevinSpiritKevinSpirit Posts: 2Questions: 0Answers: 0
    edited June 2020

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    Hi Kevin,

    Interesting - could you try this please:

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

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

    Thanks,
    Allan

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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 );
            } );
    
  • KevinSpiritKevinSpirit Posts: 2Questions: 0Answers: 0

    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

This discussion has been closed.