.whereIn() with multiple columns in mssql

[manscrober]

Environment

Knex version: ^0.18.3 Database + version: ms sql server 11.0.6260.1 OS: WSL Ubuntu 18.04

Select applicable template from below. For MSSql tag @smorey2.

Bug

1. Explain what kind of behaviour you are getting and how you think it should do knex.whereIn(["column1","column2"], [["value1","value2"],["value3","value4"] returns select * from [mytable] where ([column1], [column2]) in ((?, ?), (?, ?))

which is not supported by mssql. This results in an UnhandledPromiseRejectionWarning coming from tedious for me.

2. Error message (node:6296) UnhandledPromiseRejectionWarning: RequestError: An expression of non-boolean type specified in a context where a condition is expected, near ','. at handleError (app/node_modules/mssql/lib/tedious.js:566:15) at Connection.emit (events.js:198:13) at Connection.EventEmitter.emit (domain.js:448:20) at Parser.tokenStreamParser.on.token (app/node_modules/mssql/node_modules/tedious/lib/connection.js:716:12) at Parser.emit (events.js:198:13) at Parser.EventEmitter.emit (domain.js:448:20) at Parser.parser.on.token (app/node_modules/mssql/node_modules/tedious/lib/token/token-stream-parser.js:27:14) at Parser.emit (events.js:198:13) at Parser.EventEmitter.emit (domain.js:448:20) at addChunk (app/node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:297:12) at readableAddChunk (app/node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:279:11) at Parser.Readable.push (app/node_modules/mssql/node_modules/readable-stream/lib/_stream_readable.js:240:10) at Parser.Transform.push (app/node_modules/mssql/node_modules/readable-stream/lib/_stream_transform.js:139:32) at doneParsing (app/node_modules/mssql/node_modules/tedious/lib/token/stream-parser.js:80:14) at token (app/node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:48:5) at call.lineNumber (app/node_modules/mssql/node_modules/tedious/lib/token/infoerror-token-parser.js:13:19) From previous event: at Client_MSSQL._query (app/node_modules/knex/src/dialects/mssql/index.js:291:12) at Client_MSSQL.query (app/node_modules/knex/src/client.js:158:17) at Runner.query (app/node_modules/knex/src/runner.js:136:36) at app/node_modules/knex/src/runner.js:40:23 From previous event: at Runner.run (app/node_modules/knex/src/runner.js:26:16) at Builder.Target.then (app/node_modules/knex/src/interface.js:14:43) at QueryBuilderService.execQuery (app/backend/src/persistence/query-builder.service.ts:11:58) at Promise (app/backend/src/business/query-adapter.service.ts:12:37) at new Promise (<anonymous>) at QueryAdapterService.<anonymous> (app/backend/src/business/query-adapter.service.ts:11:17) at Generator.next (<anonymous>) at app/node_modules/tslib/tslib.js:110:75 at new Promise (<anonymous>) at Object.__awaiter (app/node_modules/tslib/tslib.js:106:16) at QueryAdapterService.execQuery (app/backend/src/business/query-adapter.service.ts:10:24) at Promise (app/backend/src/controllers/data.ts:17:26) at new Promise (<anonymous>) at app/backend/src/controllers/data.ts:16:18 at Generator.next (<anonymous>) at app/node_modules/tslib/tslib.js:110:75 at new Promise (<anonymous>) (node:6296) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1) (node:6296) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

3. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real database connection to MySQL or PostgreSQL, then single file example which initializes needed data and demonstrates the problem. see number 1 - doesn't quite need more examples

Feature discussion / request

1. Explain what is your use case I wanted to use knex for this, to automate the process of building my own string with where (column1=value1 and column2=value2)or(column1=value3 and column2=value4). This is useful because other than orWhere this can be done with an object of variable size.

2. Explain what kind of feature would support this

3. Give some API proposal, how the feature should work where params is an object passed via a post request, e.g.: params={columns:["col1","col2"],items:[["val1","val2"], ["val3","val4"]]}; columns = params["columns"];items=params["items"] this would result in a parameterized query where one could later add their values

let filterstring:string=Object.keys(items).map(k=> "("+ Object.keys(items[k]).map(v=> columns[v]+" = '"+items[k][v]+"'" ).join(" and ")+ ")" ).join(" or "); the output for my example would be: where (col1=@col10 and col2=col20) or (col1=@col11 and col2=col21) and then the parameters would be added accordingly.

[fer22f]

I recommend instead of chaining and and ors, the use of VALUES, such as the way Objection does.

SELECT * FROM [mytable]
WHERE EXISTS (
  SELECT * FROM (VALUES (?, ?), (?, ?), (?, ?)) AS V(c1,c2)
  WHERE col1 = c1 AND col2 = c2
)

[rafagsiqueira]

I have the same problem. @manscrober did you manage to solve this? If so, can you share your knex call?

[manscrober]

Unfortunately I "resolved" this by not using knex but implementing the logic myself. I might also have used another library, it's a long time ago - all I remember is I stopped using knex.