Node.js-将SQL查询转换为Knex.js [英] Node.js - Converting a SQL query into Knex.js

问题描述

我有一个包含多个join的SQL查询(Postgres)，我很难将其转换为单个Knex.js语句.这是SQL查询:

I have a SQL query (Postgres) containing several joins that I'm having trouble converting into a single Knex.js statement. Here's the SQL query:

SELECT 
    User.id, User.name, User.email,
    Role.name AS r_name,
    UserPofile.id AS p_id, UserPofile.date_of_birth AS p_dob,
    AuthToken.id AS at_id, AuthToken.token AS at_token, AuthToken.platform AS at_platform
FROM public."user" User
    LEFT JOIN public."user_role" UserRole ON User.id = UserRole.user_id
    LEFT JOIN public."role" Role ON UserRole.role_id = Role.id
    LEFT JOIN public."application" Application ON UserProfile.app_id = Application.id
    LEFT JOIN public."user_profile" UserProfile ON User.id = UserProfile.user_id
    LEFT JOIN public."auth_token" AuthToken ON User.id = AuthToken.user_id
WHERE 
    User.email LIKE '[e-mail]' AND
    Application.name LIKE 'awesome-application' AND
    AuthToken.platform LIKE 'mobile';

这是我的Knex.js代码:

Here's my Knex.js code:

    return knex('user').where({ email:'[e-mail]' })
    .select([
        'user.id', 'user.name', 'user.email' // User
        'role.name AS rName' // Roles
        'user_profile.id AS pId', 'user_profile.date_of_birth AS pDob' // UserProfiles
        'auth_token.id AS atId' 'auth_token.platform AS atPlatform', 'auth_token.token AS atToken' // AuthTokens
    ])
    .leftJoin('user_profile', 'user_profile.user_id', 'user.id')
    .leftJoin('user_role', 'user_role.user_id', 'user.id')
    .leftJoin('role', 'role.id', 'user_role.role_id')
    .leftJoin('auth_token', 'auth_token.user_id', 'user.id')
    .then(users => {

        users = users.filter(user => { 
            return user.pApp_id === appId && user.atApp_id === appId && user.atPlatform === platform; 
        });
        return users;
    });

这会产生与SQL查询相同的结果，但是问题是我必须在Knex调用的.then()子句中过滤返回的用户，因为我不知道如何为WHERE添加条件Application.name和AuthToken.platform.

This produces the same result that the SQL query does, but the problem is that I have to filter the returned users in the .then() clause of the Knex call because I don't know how to add WHERE conditions for the Application.name and AuthToken.platform.

问题:

有人可以帮我弄清楚如何构造我的Knex代码的.where()子句，使其等同于SQL查询吗?

Can someone please help me figure out how to structure my Knex code's .where() clause to have it be equivalent to the SQL query?

注释:

• 我不知道如何console.log Knex生成的SQL查询，因此我不能完全确定我当前的Knex代码将在其上方生成SQL查询(减去正确的WHERE子句).我已经检查了它是否确实返回了相同的结果，方法是在PgAdmin中运行查询，然后console.log()设置从Knex函数返回的users.
• 我没有包含定义此问题中使用的表和列的CREATE TABLE/Knex迁移，因为对我而言，这没有必要，并且我不想将已经很长的问题更长.但是，如果您需要查看它，请不要犹豫，让我知道.我会很乐意包含它.

• I don't know how to console.log the SQL queries that Knex produces, therefore I'm not entirely sure that my current Knex code will produce the SQL query above it (minus the correct WHERE clause). I have checked though that it does in fact return the same results, by running the query in PgAdmin and console.log()ing the users returned in from the Knex function.
• I haven't included the CREATE TABLE / Knex migrations that defined the tables and columns used in this question, because to me it didn't feel necessary, and I don't want to make an already long question even longer. But if you need to see it, please don't hesitate to let me know. I'll gladly include it.

推荐答案

要进行调试，您可以使用.toSQL()调试knex查询备忘单

作为修补程序，您可以使用 .raw() 并将您的SQL代码粘贴到此处.

关于WHERE条件，您可以将其链接到knex查询的末尾.
像这样:

To debug you can use .toSQL() to debug your knex queries documentation.
Also, nice cheatsheet

As hot fix solution you can use .raw() and paste your SQL code there.

About WHERE conditions, you can just chain them in the end of your knex query.
Something like this:

return knex('user')
  .select('user.id', 'user.name', 'user.email', 
    'role.name AS rName'
    'user_profile.id AS pId', 'user_profile.date_of_birth AS pDob', 
    'auth_token.id AS atId' 'auth_token.platform AS atPlatform', 'auth_token.token AS atToken'
   )
  .leftJoin('user_profile', 'user_profile.user_id', 'user.id')
  .leftJoin('user_role', 'user_role.user_id', 'user.id')
  .leftJoin('role', 'role.id', 'user_role.role_id')
  .leftJoin('auth_token', 'auth_token.user_id', 'user.id') 
  .where('user.email', 'like', '[e-mail]%')
  .andWhere('application.name', 'like' '%awesome-application%')

//...etc

这篇关于Node.js-将SQL查询转换为Knex.js的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！