typeorm find row by array of ids of another table but all values in this array must be present in one relation

Question

I'm working on a chatting app.

I have table messages_thread:

@Entity("messages_thread")
export class MessagesThreadEntity {

    @PrimaryColumn({
        type: 'bigint',
        name: 'id',
    })
    id: number;

    @OneToMany(type => MessagesThreadUsersEntity, a=> a.thread)
    users!: MessagesThreadUsersEntity[];

    constructor(init?: Partial<MessagesThreadEntity>) {
        Object.assign(this, init);
    }
}

And table messages_thread_users

@Entity("messages_thread_users")
export class MessagesThreadUsersEntity {

    @PrimaryColumn({
        type: 'bigint',
        name: 'id',
    })
    id: number;

    @Column("bigint", {
        nullable: false,
        name: 'user_id',
    })
    user_id: number;

    @Column("bigint", {
        nullable: false,
        name: 'thread_id',
    })
    thread_id: number;

    @ManyToOne(type => MessagesThreadEntity, a => a.users)
    @JoinColumn({
        name: 'thread_id',
        referencedColumnName: 'id'
    })
    thread!: MessagesThreadEntity;

    constructor(init?: Partial<MessagesThreadUsersEntity>) {
        Object.assign(this, init);
    }
}

I'm trying to use createQueryBuilder to do a complicated query where I need to get the thread that has users matched to the 'user_ids' array. But there is a catch, this thread should have all these users I'm looking for, no more or fewer users.

example: I'm looking for the thread that has user_ids = [1,2,5], and I have these threads:

• thread A has users [1,2,3,4,5,6]
• thread B has users [2,5]
• thread C has users [1,2,5]

In this case, I want thread C to be returned from the database.

NOT thread A since it has more users than [1,2,5]

NOT thread B since it does not have all the users that I want.

I need the thread that has exactly the array I'm looking for, which is thread C.

What I did so far:

  let threadUsers = await this.messagesThreadUsersRepo.createQueryBuilder() 
 .select() 
 .where('affiliate_id IN (:...allUserIds)', { allUserIds }) 
 .getMany(); 

NOTE: I am using NestJs, and the database is MySQL

Answer 1

UPDATED

To get the wanted result, you have to use the HAVING clause because we should specify a condition for filtering values using a function in MySQL used to concatenate data from multiple rows into one field (GROUP_CONCAT).

 const ids= allUserIds.sort((a, b) => b - a).toString();
 return await this.messagesThreadUsersRepo.createQueryBuilder() 
    .select(["GROUP_CONCAT(user_id ORDER BY user_id DESC) as ids ","o.*"]) 
             // GROUP_CONCAT(user_id) here to get all the user ids by thread 
    .groupBy('thread_id')
    .having(`ids = '${ids}'`)
           // here we check if thread has the exact ids we want 
    .getRawMany();

FOR GLOBAL EXAMPLE WITH MYSQL QUERY using W3SCHOOL DATABASE Editor

  SELECT o.*,GROUP_CONCAT(p.ProductID) as childs FROM OrderDetails as o 
  join Products as p on o.ProductID = p.ProductID GROUP BY o.OrderID
   HAVING childs = '11';