CODEWITHSUNDEEP
javascriptnode.jspostgresqlknex.js
ewcz
ewcz

Reputation: 13087

How to query pairs with knex

I have a simple table (PostgreSQL) which contains two integer columns, say, idx and idy. I would like to use knex in order to query it to obtain all rows corresponding to a prescribed list of combinations idx/idy, e.g., to generate a query such as:

select * from "datatable" where (idx, idy) IN (('1', '10'), ('2', '20'))

Knex does offer the whereIn method, nevertheless it seems that it does not support multiple columns. I managed to achieve the result with:

const knex_conf = require('./knexfile');
const knex = require('knex')(knex_conf.development);
const pgFormat = require('pg-format');

const pairs = [ [1, 10], [2, 20] ];

var P = knex.table('datatable').whereRaw(`(idx,idy) IN ${pgFormat('(%L)',pairs)}`).toSQL();
console.log(P.sql);

but I was wondering whether there would be a more elegant solution (without the need to use pg-format or similar "external" tools).

Upvotes: 2

Views: 2658

Answers (2)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

Actually knex already support this one:

https://runkit.com/embed/f2wym1fwfrn1

const Knex = require('knex');
const knex = Knex({
  client: 'pg'
});
const pairs = [ [1, 10], [2, 20] ];
knex('datatable').whereIn(['idx','idy'], pairs).toSQL();

Upvotes: 2

dvsoukup
dvsoukup

Reputation: 1596

Knex doesn't require you to use the 'query builder' functions. In fact, as queries get more complicated, I would highly recommend to NOT use it... you'll spend far more time trying to glue all the functions together correctly vs just writing out the SQL by yourself. Let's face it, at the end of the day, knex is just spitting out sql for you anyhow. That said, something like this should work:

return knex.raw(`
    select *
    from datatable
    where (idx, idy) IN ((1, 10), (2, 20)....)
`);

Upvotes: 0

Related Questions