I'm using Knex.js in a relational database. Currently trying to do a relatively simple join, no aliases involved.

knex('tools_users')
  .select('*').from('tools_users')
  .innerJoin('users', 'users.id', 'tools_users.user_id')
  .innerJoin('tools', 'tools.id', 'tools_users.tool_id')
  .where('users.id', userId)
  .andWhere('tools.id', toolId)
  .andWhere('tools_users.current_durability', '>', 0)
  .first()
  .decrement('tools_users.current_durability', durabilityUsed)
  .then(() => {
    console.log('tool updated');
  })
  .catch((err) => {
    console.error(err);
  });

That console.error(err) is producing this error: error: missing FROM-clause entry for table "users"

Every solution I've found elsewhere online shows that it was an alias issue. I'm not using any aliases though. Not sure what else there is to do. I've found knex issues on the github repo to be inconclusive.

I'm using Knex.js in a relational database. Currently trying to do a relatively simple join, no aliases involved.

knex('tools_users')
  .select('*').from('tools_users')
  .innerJoin('users', 'users.id', 'tools_users.user_id')
  .innerJoin('tools', 'tools.id', 'tools_users.tool_id')
  .where('users.id', userId)
  .andWhere('tools.id', toolId)
  .andWhere('tools_users.current_durability', '>', 0)
  .first()
  .decrement('tools_users.current_durability', durabilityUsed)
  .then(() => {
    console.log('tool updated');
  })
  .catch((err) => {
    console.error(err);
  });

That console.error(err) is producing this error: error: missing FROM-clause entry for table "users"

Every solution I've found elsewhere online shows that it was an alias issue. I'm not using any aliases though. Not sure what else there is to do. I've found knex issues on the github repo to be inconclusive.

• javascript
• postgresql
• knex.js

Share Improve this question Follow asked Jan 6, 2017 at 23:32 Tyler MillerTyler Miller 1,35222 gold badges1010 silver badges1919 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

Knex doesn't support joining data for update queries so you have to make two separate queries... Something like these (I didn't test the queries, so there might be typos):

knex('tools_users')
  .innerJoin('users', 'users.id', 'tools_users.user_id')
  .innerJoin('tools', 'tools.id', 'tools_users.tool_id')
  .where('users.id', userId)
  .andWhere('tools.id', toolId)
  .andWhere('tools_users.current_durability', '>', 0)
  .first()
  .then((tool_user) => {
    return knex('tool_user').where('id', tool_user.id)
      .decrement('current_durability', durabilityUsed);
  })
  .then(() => {
    console.log('tool updated');
  })
  .catch((err) => {
    console.error(err);
  });

or single query with subquery

knex('tools_users')
  .decrement('current_durability', durabilityUsed)
  .whereIn('id', (subQueryBuilder) => {
    subQueryBuilder
      .from('tools_users')
      .select('id')
      .innerJoin('users', 'users.id', 'tools_users.user_id')
      .innerJoin('tools', 'tools.id', 'tools_users.tool_id')
      .where('users.id', userId)
      .andWhere('tools.id', toolId)
      .andWhere('tools_users.current_durability', '>', 0)
      .limit(1);
  })
  .then(() => {
    console.log('tool updated');
  })
  .catch((err) => {
    console.error(err);
  });