TypeORM - 带有子查询的查询生成器

介绍

我曾经是一名 .Net 开发人员，现在是一名 Node.js/Typescript 开发人员。当然，我以前也写过 SQL，并使用支持代码库 SQL 执行的LINQ 。本文介绍了如何使用TypeORM将 SQL 代码转换为 TypeScript 代码。

基本查询

TypeORM的实体模型

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class Student {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    isActive: boolean;

}

带有 TypeORM 的 TypeScript

const student = await connection
    .getRepository(Student)
    .createQueryBuilder("stu")
    .where("stu.firstName = :name", { name: "Sam" })
    .getOne();

上述代码的实际 SQL

SELECT * 
FROM Student as stu
WHERE stu.firstName = 'Sam'
LIMIT 1;

使用子查询进行查询

使用实体构建一个简单的 SELECT 查询很容易。但是，这还不足以创建图表或在表上显示计算结果。这是演示如何使用TypeORM构建复杂查询的主要部分。

使用 TypeORM 将目标 SQL 转换为 TypeScript

SELECT 
  cs.course_id as course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) AS submitted_date, 
  IFNULL(count(cs.courseId), 0) as correct_submission_number, 
  IFNULL(total_exam.number, 0) as total_number 
FROM 
  assessment as asses 
  INNER JOIN submission as sub ON asses.submission_id = sub.id 
  INNER JOIN subject_exam as se ON se.exam_id = sub.exam_id 
  INNER JOIN course_subject as cs ON cs.subject_id = se.subject_id 
  LEFT OUTER JOIN (
    SELECT 
      cs.course_id as course_id, 
      IFNULL(COUNT(cs.course_id), 0) as number 
    FROM 
      course_subject as cs 
      LEFT OUTER JOIN subject_exam as se ON cs.subject_id = se.subject_id
    WHERE
      cs.dept_id = 'abcdefg' 
    GROUP BY 
      cs.course_id
  ) as total_exam ON total_exam.course_id = cs.course_id 
WHERE 
  asses.result = '2' -- = pass
  AND asses.status = '2' -- = submitted
  AND cs.dept_id = 'abcdefg'
GROUP BY 
  cs.course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) 
ORDER BY 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) asc, 
  cs.course_id asc;

带有 TypeORM 的 TypeScript


import {getManager} from "typeorm";

// in class 'GetDailyStats'

//Build a subquery to get the total number of exams
const totalExamNumbers: any = getManager().createQueryBuilder()
    .select("cs.courseId", "course_id")
    .addSelect("IFNULL(COUNT(*), 0)", "number")
    .from(CourseSubject, "cs")
    .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id");

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalExamNumbers.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalExamNumbers.getParameters())

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: s.totalSubmissions
    };
    return item;
});

return dailyStats;

如果愿意，您可以合并主查询和子查询，但我更喜欢分开的查询，因为它具有可读性，并且可以与其他类共享子查询。

重要提示：什么是setParameters()？
在子查询中设置某些参数时，设置的值尚未准备好在主查询中执行。您需要在调用之前在主查询中明确设置它们getRawMany()。

我们需要setParameters()在主查询中调用参数吗？不，不需要。它只适用于单独的查询。

提示：IFNULLMySQL 中是什么？如果表达式为，则函数返回指定
值。IFNULL()NULL

提示：分组依据DATETIME？
如果您想更改按列分组的规则DATETIME，请更改DATE_FORMAT部分。

每小时基数：DATE_FORMAT(asses.created_datetime, '%Y-%m-%d %H:00:00')
每日基数：DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')
每月基数：DATE_FORMAT(asses.created_datetime, '%Y-%m')
每年基数：DATE_FORMAT(asses.created_datetime, '%Y')

带有可选参数的子查询

我们应该如何创建带有可选值的查询呢？不用担心，使用TypeORM很容易。

在“SubQueryBuilder”类中


import {getManager} from "typeorm";

public getTotalNumberExams(deptId? : string | undefined) {

    const subquery: any = getManager().createQueryBuilder()
        .select("cs.courseId", "course_id")
        .addSelect("IFNULL(COUNT(cs.courseId), 0)", "umber")
        .from(CourseSubject, "cs")
        .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
        .groupBy("cs.course_id");

    //Filter the list if a deptId is not undefined
    if (deptId !== undefined) {
        subquery.where("cs.deptId = :deptId", {
            deptId: deptId
        });

        return subquery;
    }
}

在“GetDailyStats”类中


import {getManager} from "typeorm";
import {getTotalNumberExams} from "SubQueryBuilder";

// in class 'GetDailyStats'

const totalNumberExams = getTotalNumberExams(deptId);

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalNumberExams.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalNumberExams.getParameters())

//Filter the list if a deptId is not undefined
if (deptId !== undefined) {
    dailyStatsQuery.andWhere("cs.deptId = :deptId", {
        deptId: deptId
    });

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: s.totalSubmissions
    };
    return item;
});

return dailyStats;

子查询的性能

一些经验丰富的开发人员提到，您不应该经常使用子查询，因为内部子查询可能会在没有索引或数据库优化的好处的情况下执行。

请查看文章TypeORM - 多个数据库调用 vs. 单个数据库调用。它或许对你有帮助。

检查生成的 SQL

TypeORM支持检查生成的 SQL 语法。只需将 from 替换getRawMany()为即可getQuery()。

在“GetDailyStats”类中


import {getManager} from "typeorm";
import {getTotalNumberExams} from "SubQueryBuilder";

// in class 'GetDailyStats'

const totalNumberExams = getTotalNumberExams(deptId);

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalNumberExams.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalNumberExams.getParameters())

//Filter the list if a deptId is not undefined
if (deptId !== undefined) {
    dailyStatsQuery.andWhere("cs.deptId = :deptId", {
        deptId: deptId
    });

// Generate an actual SQL query
const actualSqlQuery = await dailyStatsQuery.getQuery();
console.log(actualSqlQuery);

结论

TypeORM是实现 Code-First 方法的强大工具。如果我找到有用的信息，我会分享。

简历

30岁那年，我持打工度假签证前往澳大利亚转行。我毕业于悉尼大学，获得了计算机科学与技术学士学位。在此期间，我还在澳大利亚悉尼的美国运通银行担任反欺诈分析师（没错，那段时间超级忙。我白天上大学，晚上工作……）

毕业后，我在一家澳大利亚小公司担任了 5 年的 C#/.Net 开发人员。现在，我回到日本，在一家日本安全初创公司担任 TypeScript/Go/React 开发人员。

我喜欢学习新领域，这对我来说是一个很大的挑战。如果您能支持我，让我能够快速提升技能/知识，我将不胜感激。如果您对我独特的职业感兴趣，请随时联系我。

谢谢，

文章来源：https://dev.to/yoshi_yoshi/typeorm-query-builder-with-subquery-490c