TypeORM - Query Builder with Subquery
Tags: node, sql, typescript, database
Published: 2021-03-18 04:17:56

---
title:TypeORM - Query Builder with Subquery
description: The article demonstrates how complex subquery should be created with TypeORM in Node.js, TypeScript.
series: TypeORM
tags: node, sql, typescript, database
---

##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](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/) which supports the code-base SQL execution. This describes how I convert from SQL to TypeScript coding with [TypeORM](https://typeorm.io/#/).

##Basic Query

####Entity model of TypeORM
```TypeScript
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
```TypeScript
const student = await connection
    .getRepository(Student)
    .createQueryBuilder("stu")
    .where("stu.firstName = :name", { name: "Sam" })
    .getOne();
```

#####Actual SQL of the above code 
```SQL
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](https://typeorm.io/#/).

#####Target SQL converted to TypeScript with TypeORM
```SQL
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
```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;
```

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](https://typeorm.io/#/).

#####in class 'SubQueryBuilder'
```TypeScript

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'
```TypeScript

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](https://dev.to/yoshi_yoshi/typeorm-multiple-db-calls-vs-single-db-call-178). It might be helpful for you. 

##Check Generated SQL
[TypeORM](https://typeorm.io/#/) supports checking the generated SQL syntaxes. Please simplely replace from `getRawMany()` to `getQuery()`.

#####in class 'GetDailyStats'
```TypeScript

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](https://typeorm.io/#/) 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,