No products in the cart.
Recap
In the last lesson, we created all the TypeORM Entities required for a simple Learning Management System. In this lesson we will see some examples of how to query a TypeORM database in the Learning Management System (LMS).
We have the following entities: User, Course, Lesson, Enrollment, Assignment, and Grade at place. TypeORM provides us a variety of methods to interact with the database (query). These queries can use a combination of the repository pattern, relations, and query builder to fetch data efficiently.
TypeORM Querying with Real Examples
1. Finding All Courses for a User
Let’s say we want to find all the courses a user is enrolled in. This can be done via the Enrollment entity or using the ManyToMany relation in User and Course.
import { getRepository } from 'typeorm';
import { User } from './entities/User';
async function getCoursesForUser(userId: number) {
const userRepository = getRepository(User);
const user = await userRepository.findOne({
where: { id: userId },
relations: ['courses'], // Ensure we load the related courses
});
if (!user) {
throw new Error('User not found');
}
return user.courses;
}
- We use findOne to fetch a specific User by their id.
- relations: ['courses'] ensures that the associated courses for that user are fetched. This will return an array of Course objects the user is enrolled in.
2. Finding All Lessons for a Course
To fetch all lessons of a specific course, we can query the Lesson entity and filter by Course.
import { getRepository } from 'typeorm';
import { Course } from './entities/Course';
async function getLessonsForCourse(courseId: number) {
const courseRepository = getRepository(Course);
const course = await courseRepository.findOne({
where: { id: courseId },
relations: ['lessons'], // Load related lessons
});
if (!course) {
throw new Error('Course not found');
}
return course.lessons;
}
3. Find All Students Enrolled in a Specific Course
Here we query Enrollment to find all the students who are enrolled in a specific course. This involves joining User and Course through the Enrollment entity.
import { getRepository } from 'typeorm';
import { Enrollment } from './entities/Enrollment';
async function getStudentsForCourse(courseId: number) {
const enrollmentRepository = getRepository(Enrollment);
const enrollments = await enrollmentRepository.find({
where: { course: { id: courseId } },
relations: ['user'], // Load related users (students)
});
return enrollments.map((enrollment) => enrollment.user);
}
4. Find All Assignments for a Course
We can find all assignments that belong to a course using the Assignment entity, filtered by Course.
import { getRepository } from 'typeorm';
import { Course } from './entities/Course';
async function getAssignmentsForCourse(courseId: number) {
const courseRepository = getRepository(Course);
const course = await courseRepository.findOne({
where: { id: courseId },
relations: ['assignments'], // Ensure assignments are loaded
});
if (!course) {
throw new Error('Course not found');
}
return course.assignments;
}
5. Find All Grades for a Specific Student
To get all the grades for a student in a course, we can query the Grade entity and join with Assignment and User. We filter Grade records where the user matches the provided userId.
import { getRepository } from 'typeorm';
import { Grade } from './entities/Grade';
async function getGradesForStudent(userId: number) {
const gradeRepository = getRepository(Grade);
const grades = await gradeRepository.find({
where: { user: { id: userId } },
relations: ['assignment'], // Load associated assignments
});
return grades;
}
6. Find the Grade for a Student in a Specific Assignment
To find a specific grade for a student in an assignment, we can filter by both userId and assignmentId.
import { getRepository } from 'typeorm';
import { Grade } from './entities/Grade';
async function getGradeForStudentInAssignment(userId: number, assignmentId: number) {
const gradeRepository = getRepository(Grade);
const grade = await gradeRepository.findOne({
where: {
user: { id: userId },
assignment: { id: assignmentId },
},
relations: ['assignment', 'user'], // Load the assignment and user relations
});
if (!grade) {
throw new Error('Grade not found for this student in the assignment');
}
return grade;
}
7. Find all students who have completed all assignments in a course
TypeORM’s QueryBuilder allows us to build more complex queries with joins and filters.
For Example: Let us find all students who are enrolled in a specific course and have completed all assignments (i.e., they have grades for every assignment in the course).
import { getRepository } from 'typeorm';
import { Enrollment } from './entities/Enrollment';
import { Assignment } from './entities/Assignment';
async function getStudentsWithGradesForCourse(courseId: number) {
const enrollmentRepository = getRepository(Enrollment);
// Get all assignments for the course
const assignments = await getRepository(Assignment).find({
where: { course: { id: courseId } },
});
const studentIdsWithGrades = await enrollmentRepository
.createQueryBuilder('enrollment')
.innerJoinAndSelect('enrollment.user', 'user')
.leftJoinAndSelect('user.grades', 'grade')
.where('enrollment.courseId = :courseId', { courseId })
.andWhere('grade.assignmentId IN (:...assignmentIds)', {
assignmentIds: assignments.map((a) => a.id),
})
.groupBy('enrollment.userId')
.having('COUNT(grade.id) = :assignmentCount', {
assignmentCount: assignments.length,
})
.getMany();
return studentIdsWithGrades;
}
- This query uses
QueryBuilderto perform an inner join withUserand a left join withGradeto get all students who have grades for all assignments in the course. - We check if the count of grades matches the number of assignments for that course, ensuring all assignments have been graded for each student.
Depending on the specific requirements, we can extend these queries with additional filters, sorting, pagination, and more.