The problem I’m facing is that this query sometimes returns jobs where userId is not present in reviewUsers, inviteUsers, or favouriteUsers, despite expecting to retrieve all jobs where userId matches any of these fields and these fields are not null. Additionally, when a relevant job is found where the user with that userId is associated (in reviewUsers, inviteUsers, or favouriteUsers), I need to retrieve all the reviewUsers, inviteUsers, and favouriteUsers associated with that job, besides the user whose ID I use to filter.
Could someone please advise on how to correct my query to ensure it retrieves jobs correctly based on the userId association and fetches all associated users (reviewUsers, inviteUsers, favouriteUsers) for each job?
const jobs = await this.jobRepository
.createQueryBuilder('jobs')
.leftJoinAndSelect('jobs.skills', 'skills')
.leftJoinAndSelect('jobs.user', 'user')
.leftJoinAndSelect('jobs.inviteUsers', 'inviteUsers')
.leftJoinAndSelect('jobs.reviewUsers', 'reviewUsers')
.leftJoinAndSelect('jobs.favouriteUsers', 'favouriteUsers')
.where('reviewUsers.id = :id' , { id: userId })
.orWhere('inviteUsers.id = :id',{id:userId})
.orWhere('favouriteUsers.id = :id',{id:userId})
.andWhere("reviewUsers.id is not null OR inviteUsers.id is not null OR favouriteUsers.id is not null")
.getMany();
I tried rearranging the queries but that didn’t work…