时间戳

首页 > 解决方案 > Graphql 获取动态数据的聚合

node.js - Graphql 获取动态数据的聚合

问题描述

我在 oracle db 上使用带有节点 js 的 graphql 来获取一些数据。我有两个表 customer 和 sub_customer 具有以下模式：

const Customer = new GraphQLObjectType({
  description: 'Customer data schema',
  name: 'Customer',
  fields: () => ({
    name: {
      type: GraphQLString,
      sqlColumn: 'NAME',
    },
    city: {
      type: GraphQLString,
      sqlColumn: 'CITY'
    },
    region: {
      type: GraphQLString,
      sqlColumn: 'REGION'
    },
    country: {
      type: GraphQLString,
      sqlColumn: 'COUNTRY'
    },
    gender: {
      type: GraphQLString,
      sqlColumn: 'GENDER'
    },
    emp_id: {
      type: GraphQLString,
      sqlColumn: 'EMP_ID'
    },
    sub_id: {
      type: new GraphQLList(Subcustomer),
      sqlJoin: (Subcustomer, Customer, args) => `${Subcustomer}.REGION_CODE = ${Customer}.REGION`
    }
  })
});

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

const Subcustomer = new GraphQLObjectType({
  description: 'Subcustomer data schema',
  name: 'Subcustomer',
  fields: () => ({
    sub_customer_id: {
      type: GraphQLString,
      sqlColumn: 'SUB_CUSTOMER_ID',
    },
    region_code: {
      type: GraphQLString,
      sqlColumn: 'REGION_CODE'
    },
    customer: {
      type: new GraphQLList(Customer),
      args: {
        emp_id: {
          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" = '${args.emp_id}'`;
        }

        return whereClause;
      },
      sqlJoin: (Subcustomer, Customer, args) => `${Subcustomer}.REGION_CODE = ${Subcustomer}.REGION`
    }
  })
});

Subcustomer._typeConfig = {
  sqlTable: 'SUB_CUSTOMER',
  uniqueKey: 'REGION_CODE'
}

这是我的查询：

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
        });
        });
      }
    },
    sub_customer: {
      type: new GraphQLList(Subcustomer),
      args: {
        user_id: {
          description: 'User Id',
          type: GraphQLString
        },
      },
      where: (user, args, context) => {
        if (Object.keys(args).length === 0) {
          return false;
        }
        let whereClause = '1 = 1';

        if (args.user_id != undefined) {
          whereClause += `\n  AND ${user}."SUB_CUSTOMER_ID" = :user_id`;
        }

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

执行时我得到正确的结果：

{
  sub_customer(user_id: "123") {
    sub_customer_id
    customer(emp_id: "456") {
      name
    }
  }
}

为了使用静态 where 子句获取记录数，我这样做：

sqlExpr: Customer => `(SELECT count(*) FROM CUSTOMER WHERE EMP_ID = '456')`

这给了我正确的计数。但是，我想要用户应用 where 子句时返回的所有结果的计数，并且它可能是动态的。

我正在使用 join monster 作为库来获取记录。我找不到使用动态 where 子句执行此聚合的任何方法。

谢谢你。

标签： node.jsoraclegraphql

解决方案

该sqlExpr函数传递了多个参数。您可以访问：tableAlias、args（对于此字段）context、和sqlASTNode： https ://join-monster.readthedocs.io/en/latest/API/#sqlExpr

sqlASTNode 有一个父属性（不可枚举），它允许您获取父字段的参数（在您的情况下为客户）。所以你也可以让这个领域更有活力。这是一个我没有测试过的例子。

sqlExpr: (table, args, context, sqlASTNode) => {
  let expr;

  if (sqlASTNode.parent && sqlASTNode.parent.args && sqlASTNode.parent.args.emp_id != undefined) {
    expr = '(SELECT count(*) FROM CUSTOMER WHERE EMP_ID = :emp_id)';
  } else {
    expr = 'null';
  }

  return expr;
}

推荐阅读