使用knex对3个表执行联接操作 [英] Performing join operation on 3 tables using knex

问题描述

我有一个SQL查询，该查询对2个表执行innerJoin操作.

I have a SQL query which performs innerJoin operation on 2 tables.

表1:user



表2:local_auth

Table 1: user



Table 2: local_auth

查询根据user_id键返回所有匹配的行

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

现在，我要做的就是修改上面的查询，以便它对3个表执行联接操作.

Now, what I have to do is to modify the above query so that it performs the join operation on 3 tables.

表3:customer_user

Table 3: customer_user

还有一个名为customer_user的表，该表具有user_id作为外键之一.如何基于user_id从customer_user表中检索某些字段.我想执行类似的操作(但由于使用knex执行多个联接的方法不正确，因此以下操作当然无效)

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

注意:由于在SO上添加table标记的限制，因此我附上了屏幕截图.

Note: Due to the limitation of adding a table markup on SO, I have attached the screenshot instead.

推荐答案

实际上，该查询工作正常.由于语法错误，我无法执行

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

这篇关于使用knex对3个表执行联接操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！