The Where Function

We of course don't always want every row from every table. We need a way to place a WHERE clause in the query.

In a similar manner to the sqlJoin function, you can define a where function on a field. Its parameters are the table alias (generated automatically by joinMonster), the GraphQL arguments on that field, the "context" mentioned earlier, and the parent table aliases. The string returned is the WHERE condition. If a falsy value is returned, there will be no WHERE condition. We'll add another top-level field that just returns one user.

const QueryRoot = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    users: { /*...*/ },
    user: {
      type: User,
      args: {
        id: { type: GraphQLInt }
      },
      where: (usersTable, args, context) => {
        if (args.id) return `${usersTable}.id = ${args.id}`
      },
      resolve: (parent, args, context, resolveInfo) => {
        return joinMonster(resolveInfo, {}, sql => {
          return knex.raw(sql)
        })
      }
    }
  })
})

{
  user(id: 1) { 
    id, idEncoded, email, fullName
    following { fullName }
    comments { id, body }
  }
}

This where function directly interpolates user input into its clause. This if fine for integers, as the GraphQL validation will prevent malicious input. However, for strings, this is not recommended in production due to SQL injection risk. Instead, you should escape the input yourself or use an established library like sqlstring, pg-format, or pg-escape.

import escape from 'pg-escape'

const QueryRoot = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    user: {
      type: User,
      args: {
        lastName: GraphQLString
      },
      where: (usersTable, args, context) => {
        return escape(`${usersTable}.last_name = %L`, args.lastName)
      },
      // ...
    }
  })
})

Adding Context

The joinMonster function has a second parameter which is basically an arbitrary object with useful contextual information that your where functions might depend on. For example, if you want to get the logged in user, the ID of the logged in user could be passed in the second argument.

{
  //...
  // there is a GraphQL context and a Join Monster context. these are separate!
  resolve: (parent, args, context, resolveInfo) => {
    // get some info off the HTTP request, like the cookie.
    const loggedInUserId = getHeaderAndParseCookie(context)
    return joinMonster(resolveInfo, { id: loggedInUserId }, sql => {
      return knex.raw(sql)
    })
  },
  where: (usersTable, args, context) => {
    return `${usersTable}.id = ${context.id}`
  }
}

Again, don't forget to double-quote case sensitive column names.

Does your WHERE condition need to reference columns on the table from the parent field? You can reference its table alias with the fourth parameter. It's an array of the table aliases for all fields antecedent of this one, starting with the direct parent.