graphql - 来自结构化对象的 Typeorm 动态查询构建器

Question

为了在 graphql 服务器中使用，我定义了一个结构化输入类型，您可以在其中指定许多与prisma 工作方式非常相似的过滤条件:

这允许我在查询中提交结构化过滤器，例如:

{
  users(
    where: {
      OR: [{ email: { starts_with: "ja" } }, { email: { ends_with: ".com" } }],
      AND: [{ email: { starts_with: "ja" } }, { email: { ends_with: ".com" } }],
      email: {contains: "lowe"}
    }
  ) {
    id
    email
  }
}

在我的解析器中，我通过一个函数提供 args.where 来解析结构并利用 TypeOrm 的查询构建器将其转换为正确的 sql。整个函数是:

import { Brackets } from "typeorm";

export const filterQuery = (query: any, where: any) => {
  if (!where) {
    return query;
  }

  Object.keys(where).forEach(key => {
    if (key === "OR") {
      where[key].map((queryArray: any) => {
        query.orWhere(new Brackets(qb => filterQuery(qb, queryArray)));
      });
    } else if (key === "AND") {
      where[key].map((queryArray: any) => {
        query.andWhere(new Brackets(qb => filterQuery(qb, queryArray)));
      });
    } else {
      const whereArgs = Object.entries(where);

      whereArgs.map(whereArg => {
        const [fieldName, filters] = whereArg;
        const ops = Object.entries(filters);

        ops.map(parameters => {
          const [operation, value] = parameters;

          switch (operation) {
            case "is": {
              query.andWhere(`${fieldName} = :isvalue`, { isvalue: value });
              break;
            }
            case "not": {
              query.andWhere(`${fieldName} != :notvalue`, { notvalue: value });
              break;
            }
            case "in": {
              query.andWhere(`${fieldName} IN :invalue`, { invalue: value });
              break;
            }
            case "not_in": {
              query.andWhere(`${fieldName} NOT IN :notinvalue`, {
                notinvalue: value
              });
              break;
            }
            case "lt": {
              query.andWhere(`${fieldName} < :ltvalue`, { ltvalue: value });
              break;
            }
            case "lte": {
              query.andWhere(`${fieldName} <= :ltevalue`, { ltevalue: value });
              break;
            }
            case "gt": {
              query.andWhere(`${fieldName} > :gtvalue`, { gtvalue: value });
              break;
            }
            case "gte": {
              query.andWhere(`${fieldName} >= :gtevalue`, { gtevalue: value });
              break;
            }
            case "contains": {
              query.andWhere(`${fieldName} ILIKE :convalue`, {
                convalue: `%${value}%`
              });
              break;
            }
            case "not_contains": {
              query.andWhere(`${fieldName} NOT ILIKE :notconvalue`, {
                notconvalue: `%${value}%`
              });
              break;
            }
            case "starts_with": {
              query
                .andWhere(`${fieldName} ILIKE :swvalue`)
                .setParameter("swvalue", `${value}%`);
              break;
            }
            case "not_starts_with": {
              query
                .andWhere(`${fieldName} NOT ILIKE :nswvalue`)
                .setParameter("nswvalue", `${value}%`);
              break;
            }
            case "ends_with": {
              query.andWhere(`${fieldName} ILIKE :ewvalue`, {
                ewvalue: `%${value}`
              });
              break;
            }
            case "not_ends_with": {
              query.andWhere(`${fieldName} ILIKE :newvalue`, {
                newvalue: `%${value}`
              });
              break;
            }
            default: {
              break;
            }
          }
        });
      });
    }
  });

  return query;
};

哪个有效(有点)但没有像我期望的那样嵌套 AND/OR 查询(并且以前在 KNEX 中工作过)。上述函数生成 SQL:

SELECT
  "user"."id" AS "user_id",
  "user"."name" AS "user_name",
  "user"."email" AS "user_email",
  "user"."loginToken" AS "user_loginToken",
  "user"."loginTokenExpiry" AS "user_loginTokenExpiry",
  "user"."active" AS "user_active",
  "user"."visible" AS "user_visible",
  "user"."isStaff" AS "user_isStaff",
  "user"."isBilling" AS "user_isBilling",
  "user"."createdAt" AS "user_createdAt",
  "user"."updatedAt" AS "user_updatedAt",
  "user"."version" AS "user_version"
FROM "user" "user"
WHERE (email ILIKE $1)
  AND (email ILIKE $2)
  OR (email ILIKE $3)
  OR (email ILIKE $4)
  AND email ILIKE $5
-- PARAMETERS: ["ja%","%.com","ja%","%.com","%lowe%"]

但我希望看到更多类似的东西:

..... 
WHERE email ILIKE '%low%' 
AND (
    email ILIKE 'ja%' AND email ILIKE '%.com'
) AND (
    email ILIKE 'ja%' OR email ILIKE '%.com'
)

原谅那些无意义、重复的查询。我只是想说明预期的 NESTED 语句。
如何强制查询构建器函数的 AND/OR 分支按预期正确嵌套？
** 如果有人能帮我弄清楚这里的实际 typescript 类型，则加分 **

Answer 1

将其拆分为 2 个函数，以便更轻松地添加类型

在你的案例陈述中，你需要做 orWhere 或 andWhere

不要在括号上进行映射，而是将其提升一级

import { Brackets, WhereExpression, SelectQueryBuilder } from "typeorm";

interface FieldOptions {
  starts_with?: string;
  ends_with?: string;
  contains?: string;
}

interface Fields {
  email?: FieldOptions;
}

interface Where extends Fields {
  OR?: Fields[];
  AND?: Fields[];
}

const handleArgs = (
  query: WhereExpression,
  where: Where,
  andOr: "andWhere" | "orWhere"
) => {
  const whereArgs = Object.entries(where);

  whereArgs.map(whereArg => {
    const [fieldName, filters] = whereArg;
    const ops = Object.entries(filters);

    ops.map(parameters => {
      const [operation, value] = parameters;

      switch (operation) {
        case "is": {
          query[andOr](`${fieldName} = :isvalue`, { isvalue: value });
          break;
        }
        case "not": {
          query[andOr](`${fieldName} != :notvalue`, { notvalue: value });
          break;
        }
        case "in": {
          query[andOr](`${fieldName} IN :invalue`, { invalue: value });
          break;
        }
        case "not_in": {
          query[andOr](`${fieldName} NOT IN :notinvalue`, {
            notinvalue: value
          });
          break;
        }
        case "lt": {
          query[andOr](`${fieldName} < :ltvalue`, { ltvalue: value });
          break;
        }
        case "lte": {
          query[andOr](`${fieldName} <= :ltevalue`, { ltevalue: value });
          break;
        }
        case "gt": {
          query[andOr](`${fieldName} > :gtvalue`, { gtvalue: value });
          break;
        }
        case "gte": {
          query[andOr](`${fieldName} >= :gtevalue`, { gtevalue: value });
          break;
        }
        case "contains": {
          query[andOr](`${fieldName} ILIKE :convalue`, {
            convalue: `%${value}%`
          });
          break;
        }
        case "not_contains": {
          query[andOr](`${fieldName} NOT ILIKE :notconvalue`, {
            notconvalue: `%${value}%`
          });
          break;
        }
        case "starts_with": {
          query[andOr](`${fieldName} ILIKE :swvalue`, {
            swvalue: `${value}%`
          });
          break;
        }
        case "not_starts_with": {
          query[andOr](`${fieldName} NOT ILIKE :nswvalue`, {
            nswvalue: `${value}%`
          });
          break;
        }
        case "ends_with": {
          query[andOr](`${fieldName} ILIKE :ewvalue`, {
            ewvalue: `%${value}`
          });
          break;
        }
        case "not_ends_with": {
          query[andOr](`${fieldName} ILIKE :newvalue`, {
            newvalue: `%${value}`
          });
          break;
        }
        default: {
          break;
        }
      }
    });
  });

  return query;
};

export const filterQuery = <T>(query: SelectQueryBuilder<T>, where: Where) => {
  if (!where) {
    return query;
  }

  Object.keys(where).forEach(key => {
    if (key === "OR") {
      query.andWhere(
        new Brackets(qb =>
          where[key]!.map(queryArray => {
            handleArgs(qb, queryArray, "orWhere");
          })
        )
      );
    } else if (key === "AND") {
      query.andWhere(
        new Brackets(qb =>
          where[key]!.map(queryArray => {
            handleArgs(qb, queryArray, "andWhere");
          })
        )
      );
    }
  });

  return query;
};