Query builder in NestJS - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a query builder for the User entity.

NestJS

const userQuery = dataSource.getRepository(User).[1]();

Drag options to blanks, or click blank then click option'

Adelete

Bfind

Csave

DcreateQueryBuilder

Attempts:

3 left

💡 Hint

Common Mistakes

Using repository methods like find or save instead of createQueryBuilder.

The createQueryBuilder method starts building a query for the User entity.

2fill in blank

medium

Complete the code to add a WHERE clause filtering users by age.

NestJS

const users = await userQuery.where('user.age [1] :age', { age: 18 }).getMany();

Drag options to blanks, or click blank then click option'

A>=

B=

C<=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which filters only age exactly 18.

Using '<=' which filters age less than or equal to 18.

The operator >= filters users with age 18 or older.

3fill in blank

hard

Fix the error in the code to select only the 'name' and 'email' fields.

NestJS

const users = await userQuery.select(['user.name', [1]]).getMany();

Drag options to blanks, or click blank then click option'

A'name'

B'user.email'

C'user.age'

D'email'

Attempts:

3 left

💡 Hint

Common Mistakes

Using field names without alias like 'email' causes errors.

Selecting wrong fields like 'name' again or 'user.age'.

Fields must be prefixed with the alias 'user.', so 'user.email' is correct.

4fill in blank

hard

Fill both blanks to order users by creation date descending and limit results to 5.

NestJS

const users = await userQuery.orderBy('user.createdAt', [1]).[2](5).getMany();

Drag options to blanks, or click blank then click option'

A'DESC'

B'ASC'

Climit

Dtake

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ASC' instead of 'DESC' for descending order.

Using 'limit' instead of 'take' which is the correct method.

Use 'DESC' to order descending and take(5) to limit results in NestJS query builder.

5fill in blank

hard

Fill all three blanks to build a query that filters active users, selects their id and name, and orders by name ascending.

NestJS

const users = await userQuery.where('user.isActive = [1]').select(['user.[2]', 'user.[3]']).orderBy('user.name', 'ASC').getMany();

Drag options to blanks, or click blank then click option'

Atrue

Bid

Cname

Dfalse

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'false' instead of 'true' for active filter.

Selecting wrong fields or missing alias prefix.

Filter active users with true, select id and name fields, and order by name ascending.

Practice

1. What is the main purpose of using a QueryBuilder in NestJS with TypeORM?

easy

A. To build database queries safely and readably without raw SQL

B. To write raw SQL queries directly in the code

C. To create frontend UI components

D. To manage user authentication

Solution

1. Step 1: Understand QueryBuilder role

   QueryBuilder helps create database queries in a safe and readable way, avoiding raw SQL.

2. Step 2: Identify incorrect options

   Options B, C, and D do not describe QueryBuilder's purpose correctly.

3. Final Answer:

   To build database queries safely and readably without raw SQL -> Option A

4. Quick Check:

   QueryBuilder purpose = safe, readable queries [OK]

Hint: QueryBuilder avoids raw SQL for safer queries [OK]

Common Mistakes:

• Confusing QueryBuilder with raw SQL usage
• Thinking QueryBuilder manages UI or auth
• Assuming QueryBuilder is for frontend

2. Which of the following is the correct way to start a QueryBuilder for an entity named User in NestJS with TypeORM?

easy

A. this.userRepository.startQuery('user')

B. this.userRepository.queryBuilder('user')

C. this.userRepository.buildQuery('user')

D. this.userRepository.createQueryBuilder('user')

Solution

1. Step 1: Recall QueryBuilder method

   The correct method to start a QueryBuilder is createQueryBuilder.

2. Step 2: Match method with options

   Only this.userRepository.createQueryBuilder('user') uses createQueryBuilder correctly.

3. Final Answer:

   this.userRepository.createQueryBuilder('user') -> Option D

4. Quick Check:

   Start QueryBuilder = createQueryBuilder() [OK]

Hint: Use createQueryBuilder() to start queries [OK]

Common Mistakes:

• Using incorrect method names like queryBuilder or buildQuery
• Forgetting to call createQueryBuilder on repository
• Passing wrong arguments to createQueryBuilder

3. Given the following code snippet, what will be the output of users?

const users = await this.userRepository
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .getMany();

medium

A. A single user with age exactly 18

B. An array of users with age greater than 18

C. An array of all users regardless of age

D. An error because of wrong parameter syntax

Solution

1. Step 1: Analyze the where condition

   The query filters users where age > 18, so only users older than 18 are selected.

2. Step 2: Understand getMany() result

   getMany() returns an array of matching users, not a single user or error.

3. Final Answer:

   An array of users with age greater than 18 -> Option B

4. Quick Check:

   Where age > 18 + getMany() = filtered array [OK]

Hint: getMany() returns all matching records as array [OK]

Common Mistakes:

• Confusing getMany() with getOne()
• Misreading the where condition syntax
• Expecting all users without filter

4. Identify the error in this QueryBuilder code snippet:

const user = await this.userRepository
  .createQueryBuilder('user')
  .where('user.name = :name', { username: 'Alice' })
  .getOne();

medium

A. The parameter name in where clause does not match the object key

B. Missing await keyword before createQueryBuilder

C. Using getOne() instead of getMany() causes error

D. The alias 'user' is not allowed in QueryBuilder

Solution

1. Step 1: Check parameter binding

   The where clause uses :name but the object key is username, causing mismatch.

2. Step 2: Understand effect of mismatch

   This mismatch means the parameter :name is undefined, causing query error or no results.

3. Final Answer:

   The parameter name in where clause does not match the object key -> Option A

4. Quick Check:

   Parameter keys must match placeholders [OK]

Hint: Match parameter keys exactly with placeholders [OK]

Common Mistakes:

• Using different names for parameters and placeholders
• Forgetting to await async calls
• Misunderstanding getOne() usage

5. You want to fetch users who are either older than 30 or have the role 'admin'. Which QueryBuilder code correctly implements this condition?

hard

A. this.userRepository.createQueryBuilder('user')
.where('user.age > 30 OR user.role = admin')
.getMany();

B. this.userRepository.createQueryBuilder('user')
.where('user.age > :age AND user.role = :role', { age: 30, role: 'admin' })
.getMany();

C. this.userRepository.createQueryBuilder('user')
.where('user.age > :age', { age: 30 })
.orWhere('user.role = :role', { role: 'admin' })
.getMany();

D. this.userRepository.createQueryBuilder('user')
.where('user.age > :age OR user.role = :role', { age: 30 })
.getMany();

Solution

1. Step 1: Understand the OR condition

   We want users older than 30 OR with role 'admin', so we use where and orWhere.

2. Step 2: Check parameter usage

   this.userRepository.createQueryBuilder('user') .where('user.age > :age', { age: 30 }) .orWhere('user.role = :role', { role: 'admin' }) .getMany(); correctly uses where with age and orWhere with role, both with parameters.

3. Step 3: Identify errors in other options

   this.userRepository.createQueryBuilder('user') .where('user.age > :age AND user.role = :role', { age: 30, role: 'admin' }) .getMany(); uses AND instead of OR. this.userRepository.createQueryBuilder('user') .where('user.age > 30 OR user.role = admin') .getMany(); lacks parameter binding. this.userRepository.createQueryBuilder('user') .where('user.age > :age OR user.role = :role', { age: 30 }) .getMany(); misses role parameter.

4. Final Answer:

   this.userRepository.createQueryBuilder('user') .where('user.age > :age', { age: 30 }) .orWhere('user.role = :role', { role: 'admin' }) .getMany(); -> Option C

5. Quick Check:

   OR condition = where + orWhere with params [OK]

Hint: Use where() then orWhere() for OR conditions [OK]

Common Mistakes:

• Using AND instead of OR in where clause
• Not binding parameters properly
• Combining conditions in one where without orWhere