CODEWITHSUNDEEP
javascriptpostgresqlknex.js
Tyler Miller
Tyler Miller

Reputation: 1342

Knex error: missing FROM-clause entry for table

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.

Upvotes: 7

Views: 4658

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

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

Upvotes: 5

Related Questions