how to use 'and' within the 'on' condition of 'join' in knex.js

Question

I want to implement below sql code in knex.js:

select c.id,c.parent_id,c.comment,u.username,c.postid from comments as
     c join post_details as p on (p.id = c.postid and c.postid=15)join
     users as u on (u.id = c.userid);

I tried this by doing :

db('comments AS c')
  .join('post_details AS p', function () {
    this.on('p.id', '=', 'c.postid').on('c.postid', '=', db.raw('?', [postid]));
  })
  .join('users AS u', 'u.id', '=', 'c.userid')
  .select(['c.id', 'c.parent_id', 'c.comment', 'u.username', 'c.postid', 'c.userid'])
  .then((data) => {
    console.log(data);
    res.json(data);
  })
  .catch((err) => res.status(400).json('unable to fetch'));

but I am getting unable to fetch while calling the URL .

so plz help in this . Thanks in advance .

Answer 1

Try this:

db('comments AS c')
  .join('post_details AS p', (joinBuilder) => {
    return joinBuilder.on('p.id', '=', 'c.postid').andOn('c.postid', '=', db.raw('?', [postid]));
  })
  .join('users AS u', 'u.id', '=', 'c.userid')
  .select(['c.id', 'c.parent_id', 'c.comment', 'u.username', 'c.postid', 'c.userid'])
  .then((data) => {
    console.log(data);
    res.json(data);
  })
  .catch((err) => res.status(400).json('unable to fetch'));