I'm trying to build a query with knex but I'm having a lot of trouble on how to implement the 'WHERE' part with the condition EXISTS (SELECT * FROM caregiver_patient WHERE patient_id IN (0,1)).
Here's the original query on SQL:
SELECT * FROM users
JOIN caregivers ON users.id = caregivers.user_id
JOIN caregiver_schedule ON caregivers.id = caregiver_schedule.caregiver_id
JOIN caregiver_patient ON caregivers.id = caregiver_patient.caregiver_id
JOIN patients ON caregiver_patient.patient_id = patients.id
WHERE caregiver_schedule.week_day = 2
AND caregiver_schedule.from_time <= 1320
AND caregiver_schedule.to_time > 1320
AND EXISTS (SELECT * FROM caregiver_patient WHERE patient_id IN (0,1));
And here's the code I have so far with Knex:
const caregivers = await db("caregivers")
.whereExists(function () {
this.select("caregiver_schedule.*")
.from("caregiver_schedule")
.whereRaw("`caregiver_schedule`.`caregiver_id` = `caregivers`.`id`")
.whereRaw("`caregiver_schedule`.`week_day` = ??", [Number(week_day)])
.whereRaw("`caregiver_schedule`.`from_time` <= ??", [timeInMinutes])
.whereRaw("`caregiver_schedule`.`to_time` > ??", [timeInMinutes]);
})
.join("users", "caregivers.user_id", "=", "users.id")
.join("patients", "caregiver_patient.patient_id", "=", "patients.id")
.select([
"caregivers.*",
"users.*",
"caregiver_schedule.*",
"patients.*",
]);
All of the numbers on the original query should be variables. The numbers inside IN should be an array.
Could anyone help me finish building this query on Knex?
The example knex query you gave doesn't really resemble the original SQL query very much at all, this is what your original SQL query would look like if you wanted to convert it to a knex query:
const result = await db('users')
.innerJoin('caregivers', 'users.id', 'caregivers.user_id')
.innerJoin('caregiver_schedule', 'caregivers.id' 'caregiver_schedule.caregiver_id')
.innerJoin('caregiver_patient', 'caregivers.id', 'caregiver_patient.caregiver_id')
.innerJoin('patients', 'caregiver_patient.patient_id', 'patients.id')
.where('caregiver_schedule.week_day', Number(week_day))
.where('caregiver_schedule.from_time', '<=', timeInMinutes)
.where('caregiver_schedule.to_time', '>', timeInMinutes)
.whereExists(function() {
this.select('*')
.from('caregiver_patient')
.whereIn('patient_id', [0,1]);
})
.select('*');
This assumes that the db variable is your knex connection. I also had to guess at the variables you wanted to use to replace the hardcoded values in the original SQL, feel free to replace the variables with any you like.