How to select specific columns in typeorm querybuilder

Question

I tried to select specific columns by joining tables in typeorm.

When I see following materials there is sample code.

https://orkhan.gitbook.io/typeorm/docs/select-query-builder#joining-relations

const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .getOne();

import {Entity, PrimaryGeneratedColumn, Column, OneToMany} from "typeorm";
import {Photo} from "./Photo";

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

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

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne} from "typeorm";
import {User} from "./User";

@Entity()
export class Photo {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    url: string;

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

for example my desired result is following.where user.name =="Timber"

{
id: user.id
name: user.name
url: photo.url
}

Are there any good way to achieve this ?

Thanks

Art Olshansky · Accepted Answer · 2020-10-17 13:06:25Z

35

const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .select(['user.id', 'user.name', 'photo.url']) // added selection
    .where("user.name = :name", { name: "Timber" })
    .getOne();

By this query, you'll get:

{
  id: 1,
  name: 'Timber',
  photos: [{ url: 'someurl1' }, ..., { url: 'someurlN' }]
}

answered Oct 17, 2020 at 13:06

Art Olshansky

3,4061 gold badge23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Akhil Mohandas Over a year ago

How to assign alias for those selected columns? I tried .select(['user.id', 'user.name', 'photo.url'],['userId','username','url']), but this approach did not work

Art Olshansky Over a year ago

@AkhilMohandas try .select(['user.id AS userId', 'user.name AS username', 'photo.url AS url'])

zulqarnain Over a year ago

@ArtOlshansky I have tried 'delivery.delivery_type AS type' with getOne. unable to get alias. its working with getRawOne().but I need getOne(). stackoverflow.com/q/72901411/4909563

Nathan Dixon Over a year ago

Make sure to not put the aliases in quotation marks. E.g. this doesn't work: .select(["user"."id", "user"."name"]). Doing this only works with getRawOne(). For the mapping to work, the aliases must not be in quotes.

CHIRAG · Accepted Answer · 2021-04-14 12:11:28Z

9

When you want to select particular columns you have to use getRawOne like below,

const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .select(['user.id', 'user.name', 'photo.url']) 
    .where("user.name = :name", { name: "Timber" })
    .getRawOne();

answered Apr 14, 2021 at 12:11

CHIRAG

871 silver badge3 bronze badges

1 Comment

Mohammad Jawad Barati Over a year ago

Just wanna complete this answers: In my experience, it does not matter if you wanna use getRawMany, getRawOne, or execute, or other methods. You need to keep in mind that for some reason you need to chain your select at the end of the methods -- createQueryBuilder("tb").leftJoinAndSelect("tb.tb2", "tb2").otherMethods().select([/* fields */]).getOne();

Collectives™ on Stack Overflow

How to select specific columns in typeorm querybuilder

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related