TypeORM: Load relations with only certain specified fields

Question

Despite much research and trial and error, I unfortunately do not yet have an elegant solution for the following use case: A user has several posts, which is correctly implemented as a one-to-many relationship. I want to load the user with all his posts, but not all attributes of the posts, only certain ones.

Approach 1: Find

await this.usersRepository.find({relations: ['posts']});

Here I can't define a corresponding select restriction anywhere.

Approach 2: QueryBuilder

await this.usersRepository.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'posts').select('posts.title').getMany();

Here I define the restriction supposedly, but the select seems to refer only to user.

Am I missing something? Or is there no intended solution for my use case?

I would be very grateful for help, thanks guys!

TypeORM version 0.2.22

score 8 · Answer 1 · edited Dec 18 '22 at 06:53

I was able to get the appropriate results you're expecting. You don't have to use getRawMany if you don't want to, and need the nested objects like I do from getMany and getManyAndCount in the way my schema is built up.

That being said, if you have the correct OneToMany relation setup in your entity

@OneToMany(type => Post, post => post.user)
posts: Post

Then you should be able to hit

const userResults: any = await getRepository(entities.User)
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.posts', 'posts')
    .select([
        'posts.name',
        'user.id'
    ])
    .getMany()

Which results in

userResults User {
  id: 3,
  posts: [ Post { name: 'asdfasdfasdf' }, Post { name: 'asdf' } ] }

Hope this helps, and/or you figured this out.

score 1 · Answer 2 · edited Dec 16 '22 at 00:41

1

Use getRawMany() instead of getMany() if you want to select some fields

try

await this.usersRepository.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'posts')
.select('posts.title')
.getRawMany();

For more info : https://github.com/typeorm/typeorm/blob/master/docs/select-query-builder.md#getting-values-using-querybuilder

edited Dec 16 '22 at 00:41

Aiq0

306
1
11

answered Apr 15 '20 at 07:09

JAR

754
4
11

Thanks for the tip, it basically works! But not quite as hoped for: 1. you have to specify all properties explicitly in select, which becomes a lot very quickly. By default I want the whole user and only certain fields of posts. 2. for example, the property names of the post change to posts_title. What I wanted would be the following, as usual in TypeORM with relations: "user": { "username": "test", "email": "test@test.com", "posts": [ {"title": "title 1"}, {"title": "title 2"} ] } I'm afraid that's not gonna happen, is it? – stoniemahonie Apr 20 '20 at 08:56
'users.*' might do the work. – Yogev Smila Feb 12 '23 at 08:04

score 0 · Answer 3 · answered Mar 25 '23 at 15:01

0

if you want post title and body alone, do this

await this.usersRepository.find({
  relations: ['posts'], 
  select: {
    post: {
      title: true,
      body: true,
    }
  }
});

answered Mar 25 '23 at 15:01

Daniel Sumah

11
1

TypeORM: Load relations with only certain specified fields

3 Answers3