Node.js - Converting a SQL query into Knex.js

Question

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 '[email protected]' AND
    Application.name LIKE 'awesome-application' AND
    AuthToken.platform LIKE 'mobile';

Here's my Knex.js code:

    return knex('user').where({ email:'[email protected]' })
    .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;
    });

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.

Question:

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?

Notes:

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.

Can you just make it a procedure and pass an input? Seems like a better process. — Jacob H
– Jacob H, Commented Jan 8, 2019 at 14:44
@Jacob_H That sounds intriguing. Do you maybe have some resources I can look at to get me started? — SeriousLee
– SeriousLee, Commented Jan 9, 2019 at 19:25

Grynets · Accepted Answer · 2019-01-08 15:20:24Z

5

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', '%[email protected]%')
  .andWhere('application.name', 'like' '%awesome-application%')

//...etc

answered Jan 8, 2019 at 15:20

Grynets

2,5351 gold badge21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SeriousLee Over a year ago

Hey! Adding more .where() clauses to the query ended up working. Thanks!

Artem Mirchenko · Accepted Answer · 2019-01-08 15:18:48Z

1

Simplest solution is use `.whereRaw()`:

knex whereRaw

Example:

.leftJoin('auth_token', 'auth_token.user_id', 'user.id')
.whereRaw('User.email LIKE ?', `${your_param_here}`)
.andWhereRaw('AuthToken.platform LIKE ?', `${your_param_here}`)

answered Jan 8, 2019 at 15:18

Artem Mirchenko

2,1701 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

Node.js - Converting a SQL query into Knex.js

2 Answers 2

1 Comment

Simplest solution is use `.whereRaw()`:

Example:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Simplest solution is use .whereRaw():

Example:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Simplest solution is use `.whereRaw()`: