CODEWITHSUNDEEP
knex.js
Seegel
Seegel

Reputation: 59

Knex query builder expecting parameter in where clause If add more where clause

const result = await connection
  .select({
    NameOne: 'Table1.NameOne',
    name: 'Table1.name',
    key: 'Table1.key',
    code: 'Table1.code',
    indOne: 'Table1.ind1',
    indTwo: 'Table1.ind2',
    tcd: 'Table2.tcd',
    indThree: 'Table2.ind3',
  })
  .from('Table1')
  .join('Table2', 'Table1.col1', 'Table2.col1')
  .where('Table2.cd', 'Table1.cd')
  .where('Table2.userId', userId)
  .where('Table1.userId', userId)
  .catch((err) => err);

in above code the first where clause expecting parameter like below.

bindings: [ 'Table1.cd', 'john', 'john'] Not sure why the it is expecting to bind parameter.

My requirement is similar to like below where I will be adding more condition with actual columns from different table.

SELECT table1.nameone,
       table2.NAME,
       table1.KEY,
       table2.cd,
       table1.ind1
FROM   table1,
       table2
WHERE  table1.col1 = table2.col1
       AND table1.nameone = table1.NAME
       AND table2.userid = 'John'
       AND table1.userid = 'John';

Please let me know what would be the wrong I am doing here.

Thank you

Upvotes: 0

Views: 847

Answers (1)

felixmosh
felixmosh

Reputation: 35493

Knex.js by default assuming that it gets a value in it's Where clauses.

In order to make the binding between 2 columns, like you want, you can use whereRaw with ?? marker.

const result = await connection
  .select({
    NameOne: 'Table1.NameOne',
    name: 'Table1.name',
    key: 'Table1.key',
    code: 'Table1.code',
    indOne: 'Table1.ind1',
    indTwo: 'Table1.ind2',
    tcd: 'Table2.tcd',
    indThree: 'Table2.ind3',
  })
  .from('Table1')
  .join('Table2', 'Table1.col1', 'Table2.col1')
  .whereRaw('?? = ??', ['Table2.cd','Table1.cd'])
// ---^
  .where('Table2.userId', userId)
  .where('Table1.userId', userId)
  .catch((err) => err);

Upvotes: 1

Related Questions