TypeORM - Query Builder with Subquery

Introduction

I was a .Net developer and am a Node.js/Typescript developer at the moment. Of course, I used to write SQL and use LINQ which supports the code-base SQL execution. This describes how I convert from SQL to TypeScript coding with TypeORM.

Basic Query

Entity model of TypeORM

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

@Entity()
export class Student {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    isActive: boolean;

}

TypeScript with TypeORM

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

Actual SQL of the above code

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

Query with Subqueries

Building a simple SELECT query with entities is easy. However, this is not enough for creating graphs or displaying calculated results on the tables. This is the main part to demonstrate how we should build complex queries with TypeORM.

Target SQL converted to TypeScript with TypeORM

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;

TypeScript with TypeORM

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;

You can consolidate the main query and subquery if you want but I prefer the divided ones because of the readability and the possibility of sharing the subqueries with the other classes.

IMPORTANT: What is setParameters()?
When you set some parameters on a subquery, setting values is not ready to execute with the main query. You explicitly need to set them on your main query before calling getRawMany().

Do we need to call setParameters() for parameters on the main query? No, you do not need it. It is just for separated queries.

HINT: What is IFNULL in MySQL?
The IFNULL() function returns a specified value if the expression is NULL.

HINT: Grouping by DATETIME?
If you want to change the grouping rules by DATETIME columns, please change the DATE_FORMAT parts.

Hourly base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d %H:00:00')
Daily base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')
Monthly base: DATE_FORMAT(asses.created_datetime, '%Y-%m')
Yearly base: DATE_FORMAT(asses.created_datetime, '%Y')

Subqueries with Optional Parameters

How should we create a query with optional values? No worries, this is easy with TypeORM.

in class '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;
    }
}

in class '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;

Subquery's Performance

Some experienced developers mention you should not use subqueries often because inside subqueries might execute without the index's or DB optimization's benefits.

Please check an article, TypeORM - Multiple DB Calls vs Single DB Call. It might be helpful for you.

Check Generated SQL

TypeORM supports checking the generated SQL syntaxes. Please simplely replace from getRawMany() to getQuery().

in class '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);

Conclusion

TypeORM is a powerful tool to implement the Code-First approach. I am going to share useful information if I find it.

Bio

When I was 30 years old, I went to Australia for changing my career on a Working Holiday visa. I graduated from University of Sydney with a Bachelor of Computer Science and Technology. During the period, I also worked as a Fraud Prevention Analyst at Amex in Sydney, Australia (yes, it was super busy. I went to my uni during the day and worked at night...)

After graduation, I worked as a C#/.Net developer for an Australian small business for 5 years. Now, I came back to Japan and work as a TypeScript/Go/React developer for a Japanese security start-up company.

I love learning new fields which is a big challenge. I am happy if you support me to accelerate the improvement of my skill/knowledge. Please feel free to contact me if you are interested in my unique career.

Thanks,

Comments

[Olivier Guimbal]

Shameless plug: You can use Typeorm with pg-mem when toying with it, or to write unit tests 😊 (that a lib I developped, which emulates a postgres database in-memory)

[Stefan Schneider]

Do you know any way to prevent mapping data back to the form of the entity if the entity actually has the information?
By that I mean, is there a way to map raw data back to an existing entity without mapping them manually?

[Shani Kehati]

Thanks so much, great sub queries.

For some reason I couldn't use the params in my subquery (it would keep it as "deptId: " in the sql query itself) but that's not so bad in my case
Thanks!

[Yoshiaki Matsutomo]

Thanks, Shani!

[Владислав Юрченко (Vladislav)]

Thank you very much.

Can you make an article on how to automatically convert typeorm raw to the correct form?

Is it possible in principle?

There is no such information to be found anywhere.