Knex error: missing FROM-clause entry for table

Question

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.

Answer 1

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);
  });