Performing join operation on 3 tables using knex

Question

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.

Table 3: customer_user

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.

Answer 1

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()
    );
}