Problem with left join with contition typeorm

Question

This is my code:

 const query = this.requestRepository
      .createQueryBuilder('request')
      .leftJoinAndMapMany(
        'request.approvers',
        RequestApprover,
        'approvers',
        'request.id = approvers.requestId',
      );

And this is data I got from query:

"requests": 
[
    {
        "id": "8ceee413-521c-4e21-a75b-27048d184804",
        "status": "waiting",
        "reason": "1",
         "approvers": [
             {
                 "id": "04946109-ba35-4c08-a469-761023f33b3c",
                 "employeeCode": "EMP001",
                 "status": "waiting",
            },
            {
                "id": "a9dec055-e237-434c-897e-d877f64df4af",
                "employeeCode": "EMP002",
                "status": "approved",
            }
        ]
    }
]

The problem is that when I add contidion to get all request which have been approve by employee EMP002

query.andWhere(`approvers.employeeCode = 'EMP002'`)

The approvers[0] is missing. I know that's correct but I wonder if there is a way to get full approvers but still get requests which have EMP002 is a approver. Thank you for your attention.

Answer 1

You can do this using two queries. First, get all the ids of the request where approvers.employeeCode = 'EMP002'. Then get all the requests where id of request equals the requestIds fetched above.

const results = await this.requestRepository
    .createQueryBuilder('request')
    .leftJoinAndMapMany(
        'request.approvers',
        RequestApprover,
        'approvers',
        'request.id = approvers.requestId',
    )
    .select('request.id', 'id')
    .where('approvers.employeeCode = :empCode', { empCode: 'EMP002' })
    .getRawMany();
const requestIds = results.map(e => e.id);
const requests = await this.requestRepository
    .createQueryBuilder('request')
    .leftJoinAndMapMany(
        'request.approvers',
        RequestApprover,
        'approvers',
        'request.id = approvers.requestId',
    )
    .where('request.id IN (:...requestIds)', { requestIds: requestIds });

requests would now contain the desired result.