[Question] Is it possible to include Nth-level relationship data using find options?

[kenpederson]

If I have a Person entity with a one-to-many relationship with a Home entity, and then the Home entity has a many-to-one relationship with a HomeType entity, is there a way to get the Home entities to include their HomeType data when selecting Person's and including their Home relations?

let personsWithHomes = await Person.find({ where: { id: id }, relations: ["homes"] })

This would return my Person records with their associated Home records, but the Home records do not include their associated HomeType. I tried adding the type as an eager loaded relationship, but it still does not get included.

Is there a way to specify the sub relations to include?

let personsWithHomes = await Person.find({ where: { id: id }, relations: ["homes", "homes.homeType"] })

[daniel-lang]

You can create your query by using the query builder

let personsWithHomesAndTypes = await repository.createQueryBuilder("person")
   .innerJoinAndSelect("person.homes", "home")
   .innerJoinAndSelect("homes.homeType", "homeType")
   .getMany()

[kenpederson]

Since you are suggesting using a different method, is it safe to assume that the answer to my question is "no", that I cannot accomplish what I am trying to do using the find options?

[kenpederson]

@daniel-lang your comment combine with a more thorough reading of the doc has provided me with the answer to my question. It is in fact possible using the find options, I just needed to use the "join" option instead of the relations.

let personsWithHomes = await Person.find({
    where: {
        id: id
    },
    join: {
        alias: "person",
        leftJoinAndSelect: {
            "homes": "person.homes",
            "homeType": "homes.homeType"
        }
    }
});

Thank you for the assistance.

[daniel-lang]

You are right, sorry, I missed that

[pleerock]

I added more advanced support to relations of the find options, e.g.

const posts = await connection.getRepository(Post).find({ relations: ["photos", "user", "photos.user", "counters.author"] });

It was a hard feature to implement, because sub-relations can be joined via embeds, but I did it ;)
This feature will be available in 0.1.7. Contributions to docs are welcomed.

[recurrence]

OH WOW, excellent work @pleerock . Great to see that added!

[theigor]

@pleerock, just to confirm, when using find options, is it limited to 2 levels? I don't see any examples anywhere of using more than 2 levels of relations and I am getting errors locally using 0.1.12 like

{
    "message": "Relation \"level1.level2.level3\" was not found, please check if it is correct and really exist in your entity."
}

even though I can get to level 3 if I start at level 2 so I know the relationships are configured correctly.

An example of what I am talking about would be:

const posts = await connection.getRepository(Post).find({ relations: ["photos", "photos.user", "photos.user.profile"] });

where photos.user.profile will be "not found" but if I try to find a user and expand to profile, it would work.

Thanks!

[theigor]

And nevermind - I just came across #1504 and updated to 0.1.14 and everything works. Great job!

[jboothe]

@pleerock Just started using typeorm with graphql this weekend. Amazing library and Super Kudos 🚀 👍 🎉 on the nested relations feature! It's a must have with graphql.

If Medium-like-clapping was available here, you'd get several hundred claps 👏 from yours truly. Nice job.

[pleerock]

@jboothe thanks. If you want to use typeorm with graphql there is a framework Im currently working on - you can try it.

[jboothe]

Good to know. I'll check it out.

[biels]

This feature is super nice, but I can't find it in the documentation. Are the docs updated? Or am I missing something?

[pleerock]

@biels what docs are you talking about? I provided an example code above.

[biels]

Yes, thank you for that. But I shouldn't have to come to a github issue to learn about a feature, right?

[pleerock]

No, if you read all parts of the documentation.

[svegalopez]

@pleerock Thanks for creating this library! We are starting to use it in a production environment.
Regarding this issue, thanks for implementign this:

const posts = await connection.getRepository(Post).find({ relations: ["photos", "user", "photos.user", "counters.author"] });

It is very helpful to be able to fetch nth level relationships. However, it would be great if we could filter (maybe using .where) based on properties of the related objects. I have seen other issues where you mention that the queryBuilder is the tool for this case. But the problem with the query builder is that we wont have typed queries. Im assuming is a very hard feature to implement. Let me know if you have any plans on doing something like I mention.

[Alex0007]

@pleerock
Nested props don't work in current alpha version published on npm. But working at 0.2.8

[adesege]

I don't think this works anymore. Currently getting a typescript error when using sub-relations

Argument of type '{ relations: string[]; }' is not assignable to parameter of type 'FindOptionsWhere<User>'.
  Object literal may only specify known properties, and 'relations' does not exist in type 'FindOptionsWhere<User>'.```

even though the property exists

[Evilart86]

It's very cool!)
But. You have more specific topics for relations in the Docs such as whole section "Relations" or topic "Working with Relations", but you put this example to "Find Options". I'd never look for it there... As result I have spent too much time to find this solution((

Yes, I know that this is my problem, but maybe can you put few examples in this section/topic

[Kononnable]

@Evilart86 Contributions to make documentation better are always welcomed.

[mbret]

I tried to load nested relations as the doc suggest but no matter what I do I always get an error.
The message looks usually like this
Relation with property path title in entity was not found. with title being my property.
Is the current version of the package broken ?

[MarkJAmes1217]

I added more advanced support to relations of the find options, e.g.

const posts = await connection.getRepository(Post).find({ relations: ["photos", "user", "photos.user", "counters.author"] });

It was a hard feature to implement, because sub-relations can be joined via embeds, but I did it ;)
This feature will be available in 0.1.7. Contributions to docs are welcomed.

i try this code and got an error relation * was not found, so i change my code with this :

const posts = await connection.getRepository(Post).find({ relations: ["photos", "photos.user", "counters.author"] })

i remove the user entity and works fine

[midhunadarvin]

I am not getting this feature. I have used it like this. Is there anything i am missing. I am only getting the first relation, that is user. The version of typeorm is 0.2.13

const response = await getManager().find(Post, {
    relations: ["user", "user.company"]
});

These are my entities

@Entity()
export class Post {
    @PrimaryGeneratedColumn("uuid")
    public id: string;

    @ManyToOne(() => User, (user) => user.id, { nullable: false })
    @JoinColumn({ name: "user_id" })
    public user: User;
}

@Entity()
export class User {

  @PrimaryGeneratedColumn("uuid")
  public id: string;

  @ManyToOne(() => Company, (company) => company.id)
  public company: Company;
}

@Entity()
export class Company {

  @PrimaryGeneratedColumn()
  public id!: number;

  @Column({ nullable: false })
  public name!: string;

}

[romcok]

It's in the documentation but it's not working nor in the @next. I had a lot of interesting moments with it.
https://typeorm.io/#/find-options/basic-options

userRepository.find({ relations: ["profile", "photos", "videos", "videos.video_attributes"] });

[kastudillo]

thanks @pleerock !

[iamchathu]

Does this works for next level too? like video.video_attributes.uploadedBy

[ItsWendell]

Hi, I wanna do something similar but then for mapping.

I have a company entity, a reviews entity and an industry entity, I wanna get a company by Id with all the linked industries to it's linked reviews. I managed to get all the reviews with their industry using the following query:

      const company = await this.companyRepository
        .createQueryBuilder('company')
        .where({
          'company.id': companyId
        })
        .leftJoinAndSelect("company.agency", "agency")
        .leftJoinAndSelect('company.reviews', 'review')
        .leftJoinAndSelect('review.industry', 'industry')
        .loadRelationCountAndMap('company.like_count', 'company.like')
        .loadRelationCountAndMap('company.review_count', 'company.reviews')
        .getOne();

Now what I actually want is to add a industries property to company that links all the industries linked to the review, any tips?

[t-heu]

FindRelationsNotFoundError: Relation "photos.url" was not found, please check if it is correct and really exist in your entity

const users = await repoUser.find({
    select: ['email', 'username'],
    relations: ["photos", "photos.url"],
})

Entitys

• Photo

@Entity()
export class Photo {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  url: string;

  @ManyToOne(type => User, user => user.photos)
  user: User;
}

• User

@Entity('User')
export class User {
    @PrimaryGeneratedColumn('uuid')
    id: string

    @Column()
    username: string;

    @Column()
    email: string;

    @Column()
    password: string;

    @CreateDateColumn({ type: 'timestamp' })
    createdAt: Date;

    @UpdateDateColumn({ type: 'timestamp' })
    updatedAt: Date;

    @OneToMany(type => Photo, photo => photo.user)
    photos: Photo[];

    @BeforeInsert()
    async hashPassword(): Promise<void> {
      const hash = await bcrypt.hash(this.password, 8)
      this.password = hash
    }
}

[ovichowdhury]

Excellent feature. using like this and worked well.
findOne(id,{ relations: ["nominees", "nominees.guardian"] });

[mashedrooms]

I added more advanced support to relations of the find options, e.g.

const posts = await connection.getRepository(Post).find({ relations: ["photos", "user", "photos.user", "counters.author"] });

It was a hard feature to implement, because sub-relations can be joined via embeds, but I did it ;)
This feature will be available in 0.1.7. Contributions to docs are welcomed.

you the best m8

[tooleks]

Is there a way to use a relation to filter a query results?

const posts = await connection
    .getRepository(Post)
    .find({
      relations: ['photos', 'user', 'photos.user', 'counters.author'],
      where: {
        'photos.name': 'Nature',
      },
    });

[bennycode]

I am getting:

error TS2769: No overload matches this call

When switchting from:

return StrategyEntity.findOne({
  relations: ['account'],
  where: {
    id: strategyId,
    account: {
      owner: {
        userId: query.userId,
        platform: query.platform,
      },
    },
  },
});

to:

return StrategyEntity.findOne({
  relations: ['account', 'account.owner'],
  where: {
    id: strategyId,
    account: {
      owner: {
        userId: query.userId,
        platform: query.platform,
      },
    },
  },
});

Tested with: typeorm v0.3.0-rc.19

[mashedrooms]

still so inefficient... :(

[Dr-Phone]

I added more advanced support to relations of the find options, e.g.

const posts = await connection.getRepository(Post).find({ relations: ["photos", "user", "photos.user", "counters.author"] });

It was a hard feature to implement, because sub-relations can be joined via embeds, but I did it ;)
This feature will be available in 0.1.7. Contributions to docs are welcomed.

Hi,

I'm trying to achieve the same result using query builder but couldn't quite get it. Can anybody help how to do the same using querybuilder?

[alkema]

@pleerock FTW.

[rojanawi-droople]

On my side it didn't work if the relationship was already eagerly loaded. That is, if in Post you have eager: true on the photos field, then having relations: ["photos.user"] didn't work (error: Relation "photos.user" was not found) and if you try to add again photos like relations: ["photos", "photos.user"] then you get another error (table name specified more than once) so I really needed to remove the eager: true and specify both relations and it worked.

Note: I am using typeorm version ^0.2.32

[kaneda-fr]

Just stumbled on this issue and I was able to implement 3 levels relations using:

.find({
        where: {
          id: userId,
        },
        relations: [
          'subscriptionPlan',
          'subscriptionPlan.subscriptionType',
          'subscriptionPlan.subscriptionType.features'
        ],
      })

[vipinjn24]

Just stumbled on this issue and I was able to implement 3 levels relations using:

.find({
        where: {
          id: userId,
        },
        relations: [
          'subscriptionPlan',
          'subscriptionPlan.subscriptionType',
          'subscriptionPlan.subscriptionType.features'
        ],
      })

me too :)

[ddpana]

How about many to many relations? Does this work?

Users have many groups, groups have many roles.
When trying this:

return await this.usersRepository.find({
  relations: ['groups', 'groups.roles']
});

Does not work:
If I leave only "groups" as a relation, it works.
Also Both Users and Groups only with one single level, it works.
I tried with eager true and false and nothing.

the code below:

Users:

@ManyToMany(() => UserEntity, user => user.groups, { createForeignKeyConstraints: false, eager: false })
  @JoinTable({
    name: "group_users",
    joinColumn: {
      name: "userId",
      referencedColumnName: "id"
    },
    inverseJoinColumn: {
      name: "groupId",
      referencedColumnName: "id"
    }
  })
  groups: GroupEntity[];

Groups:

@ManyToMany(() => GroupEntity, group => group.roles, { eager: false })
  @JoinTable({
    name: "role_groups",
    joinColumn: {
      name: "groupId",
      referencedColumnName: "id"
    },
    inverseJoinColumn: {
      name: "roleId",
      referencedColumnName: "id"
    }
  })
  roles: RoleEntity[];

[edster9]

I added more advanced support to relations of the find options, e.g.

const posts = await connection.getRepository(Post).find({ relations: ["photos", "user", "photos.user", "counters.author"] });

It was a hard feature to implement, because sub-relations can be joined via embeds, but I did it ;) This feature will be available in 0.1.7. Contributions to docs are welcomed.

Is it possible to do the same with load loadRelationIds? Example loadRelationIds: { relations: ['photos.user'] } to just load the ids also in a nested way?

[Gleb-Gaiduk]

Doesn't work for me in a situation with "entity.relation.relation" case:

 const [results, total] = await queryBuilder
        .leftJoinAndSelect(`order.products.product`, `product`)
        .take(limit)
        .skip((page - 1) * limit)
        .orderBy(`${alias}.${sortField}`, sortOrder)
        .getManyAndCount();
        ```
Getting: **Relation with property path products.product in entity was not found."**

But in the case of "entity.relation" nesting works as expected.
Any ideas?