CODEWITHSUNDEEP
javascriptnode.jsknex.js
latitudehopper
latitudehopper

Reputation: 775

Array not being passed to query in knex

I am passing an array of ids from a get query to a knex whereIn function but they are going missing.

if(query.cols){
  var cols = query.cols.map(Number);
  console.log(cols)
  search.whereIn('collection_id', cols)
}

I am mapping them to Integers for the query. The console log is...

[ 77, 66 ]

But the debug shows the query as...

...and "collection_id" in (?, ?)

What have I missed?

Upvotes: 14

Views: 22890

Answers (3)

chiragsrvstv
chiragsrvstv

Reputation: 211

According to Knex docs on raw parameter binding, we need to add ? for every element in our array that would be binded to the query:

Since there is no unified syntax for array bindings, instead you need to treat them as multiple values by adding ? directly in your query. https://knexjs.org/guide/raw.html#raw-parameter-binding

const myArray = [1,2,3]
knex.raw('select * from users where id in (' + myArray.map(_ => '?').join(',') + ')', [...myArray]);
// query will become: select * from users where id in (?, ?, ?) with bindings [1,2,3]

Upvotes: 10

gnerkus
gnerkus

Reputation: 12019

Edit:
This solution is for a previous version of Knex (v0.12.1)

The current documentation for raw bindings can be found here: https://knexjs.org/guide/raw.html#raw-parameter-binding

Old:
The values show as strings because knex requires that arrays be passed as arguments within a containing array. From the documentation for raw bindings:

Note that due to ambiguity, arrays must be passed as arguments within a containing array.

knex.raw('select * from users where id in (?)', [1, 2, 3]);

// Error: Expected 3 bindings, saw 1

knex.raw('select * from users where id in (?)', [[1, 2, 3]])

Outputs:
select * from users where id in (1, 2, 3)

You can fix this by passing the cols array within an array itself:

if (query.cols) {
  var cols = query.cols.map(Number);
  console.log(cols)
  search.whereIn('collection_id', [cols])
}

Upvotes: 17

Yunat Amos
Yunat Amos

Reputation: 99

All Credits to @chiragsrvstv

Instead of knex.row I used whereRaw here is the code

const whereClause= [ 75, 76 ]


//if array length is 0 then have this
//`select * from "users" where id!=0`
     if(whereClause.length <= 0) {
            whereClause= [ 0 ]
        }
    
        console.log(  knex('users').whereRaw("id!="+whereClause.map(_ => '?').join(' and id!='), [...whereClause]).toString())

Upvotes: 0

Related Questions