CODEWITHSUNDEEP
mysqltypeorm
hrondor
hrondor

Reputation: 127

Typeorm - using subqueries

I have got 3 entities in my database, we can imagine these 3(I kept only important columns)

User     

     @PrimaryColumn()
    nick: string

    @OneToMany(type => Connection, connection => connection.user)
    connections:Connection[] 

Connection 

    @PrimaryGeneratedColumn()
    id: number;

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

    @ManyToOne(type => Stream) 
    stream: Stream;

Stream
    @PrimaryGeneratedColumn()
    id: number;

I think that I have some skills with building Queries in typeorm, but I am stucked right now on using subquery.

What I want to do? Join all User entites on part of Connection entites(Select few of Connection entities as subquery => make leftJoin User-subquery => filter results to get connections which didnt find right(Connection) side so Connection ID is NULL ).

What I tried?

let query = await connectionRepository
.createQueryBuilder('connection')
.subQuery()
.where("connection.id = :id",{id:1})
.select(['connection.id','connection.user AS nick'])
.getQuery();

const result = await userRepository.createQueryBuilder("user")
                .leftJoinAndSelect("user.permissions", '(' + query + ')')
                .where("user.nick = :nick")
                .andWhere(`(${query}).id is null`)
                .getMany();

I also tried to use subquery without .getQuery() but It didnt work anyway, I think that I can write Ok subquery, but then I have problem to use it in the second query(with subquery attributes etc.) I have also seen some examples with lambda functions, but lambda was always on first position in the leftJoin.

Thank you for your answer:)

Upvotes: 1

Views: 3535

Answers (1)

noam steiner
noam steiner

Reputation: 4444

You need something like that:

const result = await userRepository.query(`
      SELECT user.* FROM user
      LEFT JOIN connection connection ON user.id = connection.user_id
      WHERE connection.user_id IS NULL
      AND connection.id = ?;
    `, [id])

Related question: MYSQL Left Join how do I select NULL values?

Upvotes: 1

Related Questions