Graphql execute where condition only if the argument is passed

Question

I am using graphql in node js for my oracle database wherein I connect to the remote database and fetch some details. I am fairly new to these technologies so please pardon me. I have a customer table with below schema:

const Customer = new GraphQLObjectType({
  description: 'Customer data schema',
  name: 'Customer',
  fields: () => ({
    name: {
      type: GraphQLString,
      sqlColumn: 'NAME',
    },
    city: {
      type: GraphQLString,
      sqlColumn: 'CITY'
    },
    country: {
      type: GraphQLString,
      sqlColumn: 'COUNTRY'
    },
    gender: {
      type: GraphQLString,
      sqlColumn: 'GENDER'
    },
    emp_id: {
      type: GraphQLString,
      sqlColumn: 'EMP_ID'
    }
  })
});

Customer._typeConfig = {
  sqlTable: 'CUSTOMER',
  uniqueKey: ['NAME','EMP_ID']
}

Using join monster I create my Query root as:

const QueryRoot = new GraphQLObjectType({
  description: 'global query object',
  name: 'RootQuery',
  fields: () => ({
    customer: {
      type: new GraphQLList(Customer),
      args: {
        emp_id: {
          description: 'Emp Id',
          type: GraphQLString
        },
        name: {
          description: 'Customer Name',
          type: GraphQLString
        }
      },
      where: (customer, args, context) => {
        return `${customer}."EMP_ID" = :emp_id AND ${customer}."NAME" = :name`;
      },
      resolve: (parent, args, context, resolveInfo) => {
        return joinMonster(resolveInfo, context, sql => {
          console.log('joinMaster', sql);
          return database.simpleExecute(sql, args,{
            outFormat: database.OBJECT
        });
        });
      }
    }
  })
})

When I pass my query in graphql in browser with emp_id and name parameters I get data. But there are cases when I cannot pass any parameters and would want all the rows to be fetched.

When I do not send the parameters I get error as: ORA-01008 : Not all variables bound

I want the arguments to be optional, and if I don't send them then it should return all rows.

Thank you.

Answer 1

Both the where and resolver functions are passed an args argument. This will have the parameter names and values if any. You can use that argument to build a dynamic where clause. Here's an untested example:

const QueryRoot = new GraphQLObjectType({
  description: 'global query object',
  name: 'RootQuery',
  fields: () => ({
    customer: {
      type: new GraphQLList(Customer),
      args: {
        emp_id: {
          description: 'Emp Id',
          type: GraphQLString
        },
        name: {
          description: 'Customer Name',
          type: GraphQLString
        }
      },
      where: (customer, args, context) => {
        if (Object.keys(args).length === 0) {
          return false;
        }

        let whereClause = '1 = 1';

        if (args.emp_id != undefined) {
          whereClause += `\n  AND ${customer}."EMP_ID" = :emp_id`;
        }

        if (args.name != undefined) {
          whereClause += `\n  AND ${customer}."NAME" = :name`;
        }

        return whereClause;
      },
      resolve: (parent, args, context, resolveInfo) => {
        return joinMonster(resolveInfo, context, sql => {
          console.log('joinMaster', sql);
          return database.simpleExecute(sql, args,{
            outFormat: database.OBJECT
          });
        });
      }
    }
  })
})

Since the where clause would then match the number of arguments, you shouldn't get the ORA-01008 error.