Performing join operation on 3 tables using knex
Actually, the query works just fine. Due to syntax error, I wasn't able to execute it
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()
);
}
zaq
Updated on June 17, 2022Comments
-
zaq almost 2 years
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
keyasync 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 hasuser_id
as one of the foreign key. How do I retrieve some fields fromcustomer_user
table based on theuser_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.