Select attributes on repository.find() with relations (TypeORM)

Question

My method returns a a bill object with all of User object. I would like that I return only bill object and User with two attributes in entity. I use TypeORM

  /**
   * Returns a bills by account bill
   */
  async getByAccountBill(
    accountBill: string,
    id?: number
  ): Promise<Object | undefined> {
    const userService = new UserService();
    const user = await userService.getById(id);

    const bills = await this.billRepository.find({
      select: ["accountBill"],
      where: {
        accountBill: Like(`${accountBill}%`),
        user: Not(`${user.id}`)
      },
      relations: ["user"] // I get All object Entity (userId, password, login...) I want to only name and surname
    });

    if (bills) {
      return bills;
    } else {
      return undefined;
    }
  }

Answer 1

can you try with this code -

async getByAccountBill(
    accountBill: string,
    id?: number
  ): Promise<Object | undefined> {
    const userService = new UserService();
    const user = await userService.getById(id);

    const bills = await this.billRepository.find({
      where: {
        accountBill: Like(`${accountBill}%`),
        user: Not(`${user.id}`)
      },
      relations: ["user"],
      select: {
        user: {
          id: true,
          name: true
        }
      }
    });

    if (bills) {
      return bills;
    } else {
      return undefined;
    }
  }

Answer 2

It's bit late but for all others who visit this page may be helpful there is a option available in typeorm so we can get the result how we want.

return this.repository.find({
  relations: ['user'],
  loadRelationIds: true,
  where: { ... },
  order: { ... }
});

Answer 3

You can use querybuilder which is one of the most powerful tool of TypeOrm, to do so.

const values = this.billRepository.createQueryBuilder("bill")
    .leftJoinAndSelect("bill.user", "user")
    .where("bill.accountBill LIKE :accountBill", {accountBill})
    .andWhere("user.id = :userId", {userId: user.id})
    .select(["user.name", "user.surname"])
    .execute();

// NOTE
// .execute() will return raw results.
// To return objects, use .getMany()

Answer 4

In case someone interested, short list of code related to relation and links to repo...

https://github.com/typeorm/typeorm/blob/master/src/find-options/FindOptionsUtils.ts

    /**
     * Applies give find options to the given query builder.
     */
    static applyOptionsToQueryBuilder<T>(qb: SelectQueryBuilder<T>, options: FindOneOptions<T> | FindManyOptions<T> | undefined): SelectQueryBuilder<T>;
...
        if (options.loadRelationIds === true) {
            qb.loadAllRelationIds();
        }
        else if (options.loadRelationIds instanceof Object) {
            qb.loadAllRelationIds(options.loadRelationIds);
        }

https://github.com/typeorm/typeorm/blob/master/src/query-builder/SelectQueryBuilder.ts

/**
 * Loads all relation ids for all relations of the selected entity.
 * All relation ids will be mapped to relation property themself.
 * If array of strings is given then loads only relation ids of the given properties.
 */
loadAllRelationIds(options?: { relations?: string[], disableMixedMap?: boolean }): this { // todo: add skip relations
    this.expressionMap.mainAlias!.metadata.relations.forEach(relation => {
        if (options !== undefined && options.relations !== undefined && options.relations.indexOf(relation.propertyPath) === -1)
            return;

        this.loadRelationIdAndMap(
            this.expressionMap.mainAlias!.name + "." + relation.propertyPath,
            this.expressionMap.mainAlias!.name + "." + relation.propertyPath,
            options
        );
    });
    return this;
}

/**
 * LEFT JOINs relation id and maps it into some entity's property.
 * Optionally, you can add condition and parameters used in condition.
 */
loadRelationIdAndMap(mapToProperty: string, relationName: string, options?: { disableMixedMap?: boolean }): this;

/**
 * LEFT JOINs relation id and maps it into some entity's property.
 * Optionally, you can add condition and parameters used in condition.
 */
loadRelationIdAndMap(mapToProperty: string, relationName: string, alias: string, queryBuilderFactory: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>): this;

/**
 * LEFT JOINs relation id and maps it into some entity's property.
 * Optionally, you can add condition and parameters used in condition.
 */
loadRelationIdAndMap(mapToProperty: string,
                     relationName: string,
                     aliasNameOrOptions?: string|{ disableMixedMap?: boolean },
                     queryBuilderFactory?: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>): this {

    const relationIdAttribute = new RelationIdAttribute(this.expressionMap);
    relationIdAttribute.mapToProperty = mapToProperty;
    relationIdAttribute.relationName = relationName;
    if (typeof aliasNameOrOptions === "string")
        relationIdAttribute.alias = aliasNameOrOptions;
    if (aliasNameOrOptions instanceof Object && (aliasNameOrOptions as any).disableMixedMap)
        relationIdAttribute.disableMixedMap = true;

    relationIdAttribute.queryBuilderFactory = queryBuilderFactory;
    this.expressionMap.relationIdAttributes.push(relationIdAttribute);

    if (relationIdAttribute.relation.junctionEntityMetadata) {
        this.expressionMap.createAlias({
            type: "other",
            name: relationIdAttribute.junctionAlias,
            metadata: relationIdAttribute.relation.junctionEntityMetadata
        });
    }
    return this;
}

https://github.com/typeorm/typeorm/blob/master/src/query-builder/relation-id/RelationIdAttribute.ts

/**
 * Stores all join relation id attributes which will be used to build a JOIN query.
 */
export class RelationIdAttribute {

    // -------------------------------------------------------------------------
    // Public Properties
    // -------------------------------------------------------------------------

    /**
     * Alias of the joined (destination) table.
     */
    alias?: string;

    /**
     * Name of relation.
     */
    relationName: string;

    /**
     * Property + alias of the object where to joined data should be mapped.
     */
    mapToProperty: string;

    /**
     * Extra condition applied to "ON" section of join.
     */
    queryBuilderFactory?: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>;

    /**
     * Indicates if relation id should NOT be loaded as id map.
     */
    disableMixedMap = false;
...