TypeORM Query Builder with SELECT, INNER, and LEFT JOIN Relations

INNER JOIN and LEFT JOIN are relations-related queries for SQL-based databases. On Nest.js, you use JOINS with TypeORM when creating database relations such as many to many. In this tutorial, I will teach you how to use TypeORM QueryBuilder to SELECT INNER JOIN and LEFT JOIN queries with Many-to-many relations.

TypeORM Relations with QueryBuilder INNER JOIN and LEFT JOIN

TypeORM allows you to expand your database operations with QueryBuilder. It uses SQL-related terms such as INNER JOIN, WHERE, SELECT, and LEFT JOIN. However, you can’t use these queries directly on your Nest.js code. QueryBuilder uses TypeORM-related functions to Map actual (raw) SQL queries using Typescript.

TypeORM QueryBuilder creates complex database queries with chainable TypeScript syntax. It’s like you are creating SQL queries programmatically rather than writing them as raw SQL statements.

TypeORM will give you all options, including creating relations based on how entities are related in a database. It includes such as one-to-one, one-to-many, and many-to-many.

In that case, INNER JOIN and LEFT JOIN will use TypeORM QueryBuilder to retrieve records from two tables:

• INNER JOIN selects a record only if there is a match between the columns being compared in two tables. You fetch only the records that have corresponding entries in both tables.
• LEFT JOIN retrieves all records from the left table and the matched records from the right table. This is regardless of whether there are matching records in the right table.

Setting up a Nest.js Many to Many Relationships App

Along this guide, I will give examples of using QueryBuilder with relations, LEFT JOIN, and INNER JOIN.

I have already created a complete project for TypeORM Many to Many Relations. It helps you Master TypeORM ManyToMany Relation with NestJS and Postgres. Check this guide, follow along, and you’ll be ready to use examples discussed here practically.

I will use the TypeORM Many-to-Many App with Student and Course tables. Once your app is ready, you should have the following tables with a Many-to-Many Relationship:

On the same, I have this guide that shows you how to use TypeORM OneToMany and ManyToOne with Nest.js and PostgreSQL

Let’s dive in and create TypeORM QueryBuilder examples with relations, JOIN, and INNER JOIN.

TypeORM QueryBuilder with ManyToMany Relations and INNER JOIN

Let’s say you want to get Courses with Students. Here, you want to fetch all available Courses but still get each Course with the Students assigned to them. You’ll use QueryBuilder to execute an INNER JOIN.

Because you are using TypeORM, you won’t use the raw Inner Join statements like the one below.

SELECT * FROM course
INNER JOIN student ON student.courseId = course.id;

This query should be represented on your code. TypeORM uses the innerJoinAndSelect method to represent it as follows: This example goes in your course service (provider) file:

  async getCoursesWithStudents(): Promise<Course[]> {
    return this.courseRepository
      .createQueryBuilder('course')
      .innerJoinAndSelect('course.students', 'student')
      .getMany();
  }

• createQueryBuilder('course') initialize a new instance of the QueryBuilder. In this case, course should be the name of the course entity (it doesn’t need to start with caps). The string ‘course’ is an alias for the Course entity in the SQL query.
• innerJoinAndSelect('course.students', 'student') performs the actual INNER JOIN with the ‘students’ property of the ‘course’ entity. This way, only courses with associated students will be selected.
• student is the database table alias for the joined students relation. It references the students in the SELECT clause of the SQL query.
• getMany() will query and select the result as an array of Course entities. It means you will get Course objects, each with its associated student’s property.

All you now need to add is a controller to create a GET endpoint in your course controller file:

  @Get()
  async getAllCourses(): Promise<Course[]> {
    return this.courseService.getCoursesWithStudents();
  }

Here are the results of this GET request:

[
  {
    "id": 11,
    "name": "Mathematics",
    "students": [
      {
        "id": 21,
        "name": "John Smith"
      },
      {
        "id": 30,
        "name": "Grace Anderson"
      },
      {
        "id": 27,
        "name": "William Clark"
      }
    ]
  },
  {
    "id": 12,
    "name": "History",
    "students": [
      {
        "id": 21,
        "name": "John Smith"
      },
      {
        "id": 23,
        "name": "Michael Davis"
      },
      {
        "id": 25,
        "name": "Daniel Miller"
      },
      {
        "id": 26,
        "name": "Olivia Brown"
      },
    ]
  }
]

If you take a Course with ID 13 (Computer Science), you can see TypeORM has QueryBuilder perfectly executing INNER JOIN, where each course has its related students.

The same example will work perfectly if the INNER JOIN is being executed from the student table. Assuming you want to get all students with the related courses, you will create a simple method in the student service to execute TypeORM QueryBuilder and INNER JOIN as follows:

  async getStudentsWithCourses(): Promise<Course[]> {
    return this.courseRepository
      .createQueryBuilder('course')
      .innerJoinAndSelect('course.students', 'student')
      .getMany();
  }

You will again create a controller to run a get request to this method:

  async getStudentsWithCourses(): Promise<Student[]> {
    return this.studentRepository
      .createQueryBuilder('student')
      .innerJoinAndSelect('student.courses', 'course')
      .getMany();
  }

Check results with a perfect TypeORM Inner Join response:

[
  {
    "id": 21,
    "name": "John Smith",
    "courses": [
      {
        "id": 11,
        "name": "Mathematics"
      },
      {
        "id": 12,
        "name": "History"
      },
      {
        "id": 13,
        "name": "Computer Science"
      }
    ]
  },
  {
    "id": 23,
    "name": "Michael Davis",
    "courses": [
      {
        "id": 12,
        "name": "History"
      },
      {
        "id": 14,
        "name": "Physics"
      },
      {
        "id": 15,
        "name": "English"
      }
    ]
  }
]

Expanding TypeORM INNER JOIN options with QueryBuilder

The above examples are simple. They are the basic examples you can have.

Assume you want to retrieve students with their courses and sort the results by course name in ascending order. TypeORM will expand QueryBuilder with orderBy as follows:

  async getCoursesWithStudentsAndSortByStudentName(): Promise<Course[]> {
    return this.courseRepository
      .createQueryBuilder('course')
      .innerJoinAndSelect('course.students', 'student')
      .orderBy('student.name', 'ASC')
      .getMany();
  }

You may as well need need to use other clauses such as WHERE, GROUP BY, and COUNT. Check this How to Create QueryBuilder with TypeORM and NestJS guide to learn additional clauses QueryBuilder can add to your queries.

TypeORM QueryBuilder with Relations and LEFT JOIN

How do we use TypeORM and QueryBuilder to run a LEFT JOIN? You will use a leftJoinAndSelect from TypeORM. Here is a raw SQL query. It retrieves Courses with Students (LEFT JOIN):

SELECT * FROM course
LEFT JOIN student ON student.courseId = course.id;

In your code, you will need to substitute the innerJoinAndSelect method with leftJoinAndSelect as follows:

  async getCoursesWithStudents(): Promise<Course[]> {
    return this.courseRepository
      .createQueryBuilder('course')
      .leftJoinAndSelect('course.students', 'student')
      .getMany();
  }

leftJoinAndSelect represents LEFT JOIN to select all rows from the left table and the matched rows from the right table.

Why Use TypeORM QueryBuilder with Relations and Joins?

• TypeORM QueryBuilder creates a high-level and abstracted way to build readable SQL queries.
• You have the type safety of Typescript to eliminate runtime errors.
• TypeORM QueryBuilder, alongside relations and joins, makes your code more modular as your data model evolves.

Conclusion

This guide showed you the WHYs and WHATs to start using TypeORM QueryBuilder when working with Relations. You have learned how to use a many-to-many scenario to execute INNER, and LEFT JOINS queries and interact with a relational database.