typeorm+nestjs的使用(二)

查询

const [data, total] =
  (await createQueryBuilder) <
  T >
  (T, tableName)
    .skip(queryParams.pagination.page * queryParams.pagination.limit)
    .take(queryParams.pagination.limit)
    .where(customCondition)
    .orderBy(orderByCondition)
    .getManyAndCount();
return new Pagination() < T > { data, total };

graphql 的使用

# Write your query or mutation here
query ($queryParams: QueryParams!) {
  accounts(queryParams: $queryParams) {
    total
    pageTotal
    data {
      id
      remarks
      verifiedName
      prevUserId
      balances {
        id
        walletType
        available
      }
    }
  }
}

find 使用

userRepository.find({
    select: ["firstName", "lastName"],
    relations: ["profile", "photos", "videos"],
    where: {
        firstName: "Timber",
        lastName: "Saw"
    },
    order: {
        name: "ASC",
        id: "DESC"
    },
    skip: 5,
    take: 10,
    cache: true
});

LessThan <
LessThanOrEqual <=   likes: LessThanOrEqual(10)
MoreThan >
MoreThanOrEqual >=
Equal =
Like title: Like("%out #%")
Between likes: Between(1, 10)
In title: In(["About #2", "About #3"])
Any title: Any(["About #2", "About #3"])
IsNull title: IsNull()
Raw Raw("1 + likes = 4")
SELECT * FROM "post" WHERE 1 + "likes" = 4
Not
likes: Not(MoreThan(10)),
title: Not(Equal("About #2"))
SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')

1.

const cards = await this.qbitCardRepo
  .createQueryBuilder("q")
  .select(["id"])
  .where({
    id: "0053edd6-4da0-4100-9899-97b099d4d9f5",
  })
  .leftJoinAndSelect(
    (subQuery) => {
      return subQuery
        .from(QbitCardTransactionRecord, "r")
        .select(["r.id", 'r."cardId"', 'r."createTime"'])
        .where({})
        .orderBy({
          'r."createTime"': "DESC",
        })
        .limit(1);
    },
    "r",
    'r."cardId" = id'
  )
  .getRawMany();

  SELECT "r".*, id FROM "qbitCard" "q" LEFT JOIN (SELECT "r"."id" AS "r_id", r."cardId", r."createTime" FROM "qbitCardTransactionRecord" "r" WHERE "r"."deleteTime" IS NULL ORDER BY r."createTime" DESC LIMIT 1) "r" ON r."cardId" = id WHERE ( "q"."id" = $1 ) AND ( "q"."deleteTime" IS NULL )

  // https://github.com/typeorm/typeorm/issues/6767

3.

let query = connection
  .createQueryBuilder(Chat, "chat")
  .leftJoin('chat.listingMembers', 'listingMembers')
  .where('listingMembers.id = :id', {id: currentUser.id})
  .leftJoinAndSelect('chat.messages', 'messages')
  .innerJoin('messages.holders', 'holders', 'holders.id = :userId', {userId: currentUser.id})
  .orderBy({
    "messages.createdAt": {order: "DESC", nulls: "NULLS LAST"},
    "chat.id": "DESC",
  });

  SELECT "q"."id" AS "q_id", "r"."id" AS "r_id", r."createTime" FROM "qbitCard" "q" LEFT JOIN "qbitCardTransactionRecord" "r" ON r."cardId" = "q"."id" WHERE ( "q"."id" = $1 ) AND ( "q"."deleteTime" IS NULL ) ORDER BY r."createTime" DESC NULLS LAST -- PARAMETERS: ["0053edd6-4da0-4100-9899-97b099d4d9f5"]
``

3.

const cards = await this.qbitCardRepo
  .createQueryBuilder("q")
  .select(["q.id", "r.id", 'r."createTime"'])
  .leftJoin("qbitCardTransactionRecord", "r", 'r."cardId" = q.id')
  .where({
    id: "0053edd6-4da0-4100-9899-97b099d4d9f5",
  })
  .orderBy({
    'r."createTime"': { order: "DESC", nulls: "NULLS LAST" },
  })
  .getRawMany();

4.

const info = await this.qbitCardTransactionRecordRepo
  .createQueryBuilder()
  .select(["id", "content"])
  .where(`content::json->>'authCode'=:authCode`, { authCode: "111" })
  .andWhere(`content::json->>'cardHashID'=:cardHashID`, {
    cardHashID: val["cardHashID"],
  })
  .andWhere(
    `content::json->>'systemTraceAuditNumber'=:systemTraceAuditNumber`,
    { systemTraceAuditNumber: val.systemTraceAuditNumber }
  )
  .getRawOne();

  SELECT id, content FROM "qbitCardTransactionRecord" "QbitCardTransactionRecord" WHERE ( content::json->>'authCode'=$1 AND content::json->>'cardHashID'=$2 AND content::json->>'systemTraceAuditNumber'=$3 ) AND ( "QbitCardTransactionRecord"."deleteTime" IS NULL ) -- PARAMETERS: ["111","cc3007f2-7f4b-4a4c-89bf-65e92a0d41a9","940152"]

4.

SELECT json_array_elements("statusLog")->'status' AS status from apply WHERE id = 'adf7eabf-1230-4378-8789-c81b6ac3af55'

// https://blog.csdn.net/liangrui1988/article/details/54708391?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control

count if 使用

select count(status = 'Active' OR null) from account

sun if 使用

语法 case then 条件 then (可以是数据库字段名 | 数字) ELSE 0 END

select(`cast("sum" ( CASE q."status" WHEN 'Frozen' THEN 1 ELSE 0 END ) as integer)`, 'frozenQbitCardCount')

SELECT cast("sum" ( CASE "status" WHEN 'Frozen' THEN 1 ELSE 0 END ) as integer) AS count FROM account

SELECT SUM(CASE WHEN type = 'QbitCardAccountRechargeQbitCardGroup' THEN "recipientCost" ELSE 0 END) AS money from "Transaction"

SELECT count(case when type = 'QbitCardAccountRechargeQbitCardGroup' then 1 end) as total from "Transaction"

increment 数据库字段自增（自减一样）

await this.accountExtendRepo
      .createQueryBuilder()
      .update()
      .set({
        qbitCardCountLimit: () => {
          return `"qbitCardCountLimit" + ${data.qbitCardCountLimit}`;
        },
      })
      .where({
        accountId: data.subAccountId,
      })
      .execute();

UPDATE "accountExtend" SET "qbitCardCountLimit" = "qbitCardCountLimit" + 10, "version" = "version" + 1 WHERE "accountId" = $1 -- PARAMETERS: ["cd459ae2-3f4c-4505-a984-d14c2a85c734"]

cast 的使用 类型转换

cast (sum("a") as integer)

子查询

SELECT a.* FROM (SELECT "accountId", MAX("createTime") AS "followTime" FROM "operationLog" "OperationLog" WHERE ( "OperationLog"."businessType" IN ($1) ) AND ( "OperationLog"."deleteTime" IS NULL ) GROUP BY "accountId") "a"  ORDER BY "followTime" DESC -- PARAMETERS: ["FollowCSMAccountLog"]

const [db, parameters] = this.operationLogRepo
      .createQueryBuilder()
      .select('"accountId"')
      .addSelect(`MAX("createTime")`, 'followTime')
      .where({
        businessType: In(['FollowCSMAccountLog']),
      })
      .groupBy('"accountId"')
      .getQueryAndParameters();

    const res = await createQueryBuilder()
      .select('a.*')
      .from(`(${db})` as any, 'a')
      .setNativeParameters(parameters)
      .orderBy('"followTime"', 'DESC')
      .getRawMany();