CODEWITHSUNDEEP
sqlpostgresql
MG91
MG91

Reputation: 193

Insert a null array of objects in postgres node

I have an angular app and the users can have multiple roles.

In the client when I remove or add a role to the user I send it to nodejs as an array of objects.

"roles": [
        {
            "role_id": 1,
            "role_name": "Admin"
        },
        {
            "role_id": 2,
            "role_name": "Moderator"
        }
    ],

my query :

for (i in roles) {
    db.query(`
    INSERT INTO user_roles (role_id, user_id)
    VALUES($1, $2) 
    ON CONFLICT (role_id, user_id) 
    DO UPDATE
    SET 
    role_id = $1,
    user_id = $2`, 
    [roles[i].role_id, user_id], (err, data) => {
        if (err) {
            console.log(err)
        } else {
            console.log('inserted/deleted role')
        }
    })
}

Now, there are two issue:

1- when a user is created, they start with no roles and a null array of objects is being passed roles: [ null ] which will break the query because role.role_id is null.

2- If I want to remove a role, I am passing the below, but how do I add in the query that I want to remove the roles that don't exist in the array of objects?:

"roles": [
        {
            "role_id": 1,
            "role_name": "Admin"
        },
          //the role moderator is removed from client so it's not being passed

    ],

Upvotes: 2

Views: 566

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

I see you went with the many-to-many approach :-)

Just as PostgreSQL can make your query results into json for you, it can work with json you pass into the database. Again, I would ask the database to take care of this for you.

Instead of looping in your javascript, try something like this. It's a little verbose, but it should be easier to follow once you understand the individual parts.

db.query(`
    WITH invars AS ( -- pass your parameter values only once
      SELECT $2::INT as user_id, $1::JSONB as jdata
    ), newroles as ( -- expand to all valid role_id values, if none, then role_id will be null
      SELECT user_id, (el->>'role_id')::INT as role_id
        FROM invars
             CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(jdata) AS j(el)
    ), do_delete AS ( -- delete any existing user_roles that should not exist
      DELETE FROM user_roles
       USING invars
       WHERE invars.user_id = user_roles.user_id
         AND NOT EXISTS (SELECT 1 
                           FROM newroles
                          WHERE role_id = user_roles.role_id)
    ) -- finally insert user_roles that do not exist
    INSERT INTO user_roles (role_id, user_id)
    SELECT role_id, user_id 
      FROM newroles n
     WHERE role_id IS NOT NULL 
       AND NOT EXISTS (SELECT 1 
                         FROM user_roles
                        WHERE (role_id, user_id) = (n.role_id, n.user_id))`, 
    [JSON.stringify(roles), user_id], (err, data) => {
        if (err) {
            console.log(err)
        } else {
            console.log('inserted/deleted role')
        }
    })

Upvotes: 1

Related Questions