how to chain promises using knex with the ON CONFLICT clause?

Question

I have two tables 'temp_users' and 'ratings'

         TABLE 1 (temp_users)

temp_user_id(pkey) | ip_address | total_ratings
-----------------------------------------
                   |            |              
                   |            |              
                   |            |              
     

         TABLE 2 (ratings)

rating_id | rating | product_id | temp_user_id(fkey)
----------------------------------------------------
          |        |            |              
          |        |            |              
          |        |            |                      

I am trying to make it so that once a user tries to rate a product, a temp_user gets created with their ip address.

A user_temp_id is generated once an ip_address is inserted into the table unless the ip address already exist in the table (I am using postgres ON CONFLICT to accomplish this as shown in my code below).

Once a temp_user rate a product they cannot rate it again. In other words, a temp_user can only rate the same product once.

My code to accomplish this does NOT work when I use the 'ON CONFLICT' or 'WHERE NOT EXIST' clauses, works fine when I allow duplicates of the same ip address to be inserted . my code is as follows:

app.post("/rating", (req, res) => {
  const ip =                              // <==== this is just to get the ip address. works fine.
    (req.headers["x-forwarded-for"] || "").split(",").pop().trim() ||
    req.connection.remoteAddress ||
    req.socket.remoteAddress ||
    req.connection.socket.remoteAddress; 
  const { rating, product_id } = req.body;

 knex
      .raw(                   // <=== inserts ip and temp_user_id. returns temp_user_id
        `INSERT INTO temp_users(ip_address)    
              VALUES ('${ip}')
              ON CONFLICT (ip_address)
              DO UPDATE SET total_ratings = EXCLUDED.total_ratings
              RETURNING temp_user_id`
      )
    .then((results) => {        // <=== counts the ratings to check later if user rated before
      return knex("ratings")
        .count("*")
        .as("total")
        .where({
          product_id: product_id,
          temp_user_id: results[0].temp_user_id,
        })
        .then((data) => {         // <=== check if user rated before, if not insert new user
          if (data[0].count > 0) {
            return res.status(400).json("user already rated");
          } else {
            return knex("ratings")
              .returning("*")
              .insert({
                rating: rating,
                product_id: product_id,
                temp_user_id: results[0].temp_user_id,
              })
              .then((response) => res.json(response))
              .catch((err) => err);
          }
        });
    })
    .then((response) => res.json(response))
    .catch((err) => err);
});

Code works perfect if I use this code below, but it's inserting multiple ip addresses with different temp_user_ids which is not what I want.

So by switching this part of the code...

 knex
      .raw(
        `INSERT INTO temp_users(ip_address)
              VALUES ('::5')
              ON CONFLICT (ip_address)
              DO UPDATE SET total_ratings = EXCLUDED.total_ratings
              RETURNING temp_user_id`
      )

To this...

 knex("temp_users")
   .insert({
     ip_address: ip,
    })
    .returning("*")

Am I doing something wrong with chaining the promises? How can I make it work? Any help would be appreciated.

Answer 1

I've figured out the answer. The first promise (the one where I used the 'ON CONFLICT' clause didn't just return an array of objects with temp_user_id like I expected it to do, but instead it returned an object with many properties in it including a property named "rows" which contained the returned temp_user_id value inside it.

so I only had to change my code to results.rows[0].temp_user_id instead of results[0].temp_user_id.