How can I add relations to the response using TypeORM query builder

I cannot figure out how to get the relations. Might be easier to see the code. The below function works great. It gets a list of notes and adds the group array relations to each note object when the parameter is passed in. This typical TypeORM.

public async findAll(userId: string, params?: Params): Promise<NoteEntity[]> {
  return await this.notesRepository.findAll({
    where: { user: { id: userId } },
    relations: {
      groups: params
        ? !!Object.keys(params).find((key) => key === "groups")
        : false,
    },
  });
}

However I am attempting to add search. I have the below query which returns the list of notes paginated and sorted, but I cannot figure out how to get the group relations added to the response.

public async findAll(
  userId: string,
  pageOptionsDto?: PageOptionsDto,
): Promise<PageDto<NoteDto>> {
  const queryBuilder = this.dataSource.createQueryBuilder(NoteEntity, "note");

  queryBuilder
    .where("note.userId = :userId", { userId: userId })
    .orderBy("note.createdDate", pageOptionsDto.order)
    .skip(pageOptionsDto.skip)
    .take(pageOptionsDto.take);

  const itemCount = await queryBuilder.getCount();

  const { entities } = await queryBuilder.getRawAndEntities();

  const pageMetaDto = new PageMetaDto({ itemCount, pageOptionsDto });

  return new PageDto(entities, pageMetaDto);
}

Any assistance would be greatly appreciated. Thanks.

>Solution :

To include the group relations in your paginated and sorted query, you need to adjust your query builder to join the related groups table. In TypeORM, this can be done using the leftJoinAndSelect method. This method allows you to specify a join to another table (in your case, the groups table) and select the fields you need from the joined table.

Here’s how you can modify your findAll method to include the groups relations:

public async findAll(
  userId: string,
  pageOptionsDto?: PageOptionsDto,
): Promise<PageDto<NoteDto>> {
  const queryBuilder = this.dataSource.createQueryBuilder(NoteEntity, "note");

  // Join the groups table
  queryBuilder.leftJoinAndSelect("note.groups", "group");

  queryBuilder
    .where("note.userId = :userId", { userId: userId })
    .orderBy("note.createdDate", pageOptionsDto.order)
    .skip(pageOptionsDto.skip)
    .take(pageOptionsDto.take);

  const itemCount = await queryBuilder.getCount();

  // Get both entities and raw results
  const { entities } = await queryBuilder.getRawAndEntities();

  const pageMetaDto = new PageMetaDto({ itemCount, pageOptionsDto });

  return new PageDto(entities, pageMetaDto);
}

Explanation:
leftJoinAndSelect: This method is used to perform a left join on the groups table and select its fields. The first argument "note.groups" specifies the relation on the NoteEntity (assuming groups is the name of the relation field in NoteEntity). The second argument "group" is an alias for the groups table in the resulting SQL query.

where, orderBy, skip, take: These methods are used to filter, sort, and paginate the results. They remain unchanged from your original query.

getRawAndEntities: This method returns both the raw database results and the mapped entities. Since you’re using leftJoinAndSelect, the groups relation should be populated in the entities.

Additional Notes:
Ensure that the relation name ("note.groups") matches the property name in your NoteEntity class.
If you need to conditionally include the groups relation based on some parameter (like in your first findAll method), you can add a conditional statement to decide whether to include the leftJoinAndSelect call.
The leftJoinAndSelect will include all fields from the groups table. If you need only specific fields, you can specify them like "group.fieldName".