I have a SQL query which performs innerJoin operation on 2 tables.
Table 1: user
Table 2: local_auth

The query returns all the matching rows based on the user_id key
async getUser(id) {
return camelizeKeys(
await knex
.select(
'u.id',
'u.first_name',
'u.last_name',
'u.username',
'u.image_url',
'u.is_admin',
'u.phone',
'u.info',
'la.email'
)
.from('user AS u')
.leftJoin('local_auth AS la', 'la.user_id', 'u.id')
.where('u.id', '=', id)
.first()
);
}
Now, what I have to do is to modify the above query so that it performs the join operation on 3 tables.
There is another table called customer_user, which has user_id as one of the foreign key. How do I retrieve some fields from customer_user table based on the user_id. I would like to perform something like this (but of course the following won’t work due to the incorrect way of performing multiple joins using knex)
async getUser(id) {
return camelizeKeys(
await knex
.select(
'u.id',
'u.first_name',
'u.last_name',
'u.username',
'u.image_url',
'u.is_admin',
'u.phone',
'u.info',
'la.email',
'cu.customer_id',
'cu.department_id'
)
.from('user AS u')
.leftJoin('local_auth AS la', 'la.user_id', 'u.id')
.leftJoin('customer_user AS cu', 'cu.user_id', 'u.id')
.where('u.id', '=', id)
.first()
);
}
Note: Due to the limitation of adding a table markup on SO, I have attached the screenshot instead.
