Dynamically create .where function knexjs

Question

Is there a way to dynamically use .where with knex? I have the following:

const user = await Users.findOne({id}, "id username email");

Which does

findOne(data, returns) {
  return knex("users")
    .select(returns
      && typeof returns === "string"
        ? returns.split(" ") 
        : "*"
    )
    .where(data)
    .first();
}

That works fine. If I want id and username to match, I can do:

const user = await Users.findOne({id, username}, "id username email");

However, I need username with case insensitivity. With mongo (Mongoose), I'd do:

{username: new RegExp("^" + username + "$", "I")} but using the knex query-lab: http://michaelavila.com/knex-querylab/ That does a where username = {}

So I found out that I need to do where username ilike %username% which in knex is

.where('username', 'ilike', `%${username}%`)

So I have a new function:

//users route
const  user = await Users
  .findOneByArray(['username', 'ilike', `%${username}%`]);

//queries file
findOneByArray(data) {
    return knex("users")
      .where(...data).first();
}

The problem with this though, is if I have multiple queries now, I can't next them like I'd do with an object. What I'm currently doing for a more complex query is this chaos:

//users route
const user = await Users
  .findTokenMatchesAccount(
    ['id', '=', `${token.user_id}`],
    ['username', 'ilike', `%${username}%`],
    ['email', 'ilike', `%${email}%`]
  );

//query file
findTokenMatchesAccount(id, username, email) {
  return knex("users")
    .where(...id)
    .where(...username)
    .where(...email)
    .first();
}

3 individual .wheres for them. Is there some way to automatically/dynamically create where functions like:

//users route
const user = await Users
  .findTokenMatchesAccount(
    [
      ['id', '=', `${token.user_id}`],
      ['username', 'ilike', `%${username}%`],
      ['email', 'ilike', `%${email}%`]
    ]
  );

//query file
findTokenMatchesAccount(data) {
  return knex("users")
    .where(function() {
      for(const i in data) {
        return(where(data[i])})
          .first();
      }
    }
}

Some magic or so to take all the values in the data parameter and dynamically add .where to it. Or do I have to manually set that up as part of my query for anything I may need? (3 .wheres like seen above, maybe 4 .wheres next time if I have additional case insensitive options to find)

Or would it be easier to take the data array and create strings and use knex.raw? Though, I'm not sure that'll escape a drop table.

Answer 1

Seems like knex is implemented with the Builder Pattern. Maybe try leveraging .reduce()? It's commonly used for chaining/creating pipelines.

async function findTokenMatchesAccount(whereStmtArgs) {
  // Store the query
  let query = knex('users');

  // Pick one from the 2 options:  

  // Option 1
  whereStmtArgs.reduce((q, currentWhereStmtArg) => q.where(...currentWhereStmtArg), query);

  // Option 2 (if you're not comfortable with reduce)
  for (const whereStmtArg for whereStmtArgs) {
    query = query.where(...whereStmtArg);
  }

  return query.first();
}

const dynamicWhereStatements = [['id', '=', `${token.user_id}`], ['username', 'ilike', `%${username}%`], ['email', 'ilike', `%${email}%`]];
const user = await findTokenMatchesAccount(dynamicWhereStatements);