Postgres : Complex Multiple Update query for one to many relationship

Question

My Db looks like this https://dbfiddle.uk/?rdbms=postgres_10&fiddle=7e0cf7ad1e5bf9f4e0321a2e5ec970f7

So

• A user can have multiple Books
• Basic one to many relationship

Lets suppose if a user updates preexisting book and adds more books too, How would i go about making a query for that ?

I did some research and came to the conclusion that either cte or function would come to my rescue.

My request body ( JSON ) data FOR PATCH QUERY would look like this

{
   // user data
      user_id: 1,
      name : 'Ryan',
      books : [
      {
        book_id : 1,
        stock : 500
      },
      {
        book_id : 2,
        stock : 500
      },
      {
        // this book should be added to the users_books table
        name: 'My new book 1',
        stock : 500
      }

      ]

}

The postgresql update queries for the above data should look like ->


UPDATE users_books(stock) VALUES(500) WHERE user_id = 1 AND book_id 1;
UPDATE users_books(stock) VALUES(500) WHERE user_id = 1 AND book_id 2;
INSERT INTO users_books(user_id,book_id,stock) VALUES(1,3,500);

So looking at the structure above , i need the books_users table to updated accordingly.

My current understanding is pass the books object as jsonb to postgresql's function. Then loop through it while updating / adding books accordingly. I'm not sure how would i go about knowing whether user already has a book or not.

How would you guys transform this request body into the aforementioned complex update query ? And would doing this in a function be transactional at all?

Answer 1

You can do this all in one statement, but it would be better to have some unique constraints to prevent it from doing something bad. users_books(book_id, user_id) should be unique and books(name) should be unique.

Here's the fiddle

Here's the important part:

-- The test data
with data(d) as (VALUES ('
{
      "user_id": 1,
      "name" : "Ryan",
      "books" : [
      {
        "book_id" : 1,
        "stock" : 500
      },
      {
        "book_id" : 2,
        "stock" : 500
      },
      {
        "name": "My new book 1",
        "stock" : 500
      }

      ]

}'::jsonb)
-- Parse the json to get user_id, book_id, stock, and name for each book
), normalized_data as (
    select (d ->> 'user_id')::int as user_id,
       book_id, stock, name
    FROM data
    JOIN LATERAL (SELECT * FROM jsonb_to_recordset(d -> 'books') 
                    as books(book_id int, stock int, name text)
    ) sub ON TRUE
-- Update existing stock
), update_existing as (
  UPDATE users_books set stock = normalized_data.stock
  FROM normalized_data
  WHERE users_books.user_id = normalized_data.user_id
    AND users_books.book_id = normalized_data.book_id
    AND normalized_data.book_id IS NOT NULL
-- insert any new books
), insert_new_book as (
INSERT INTO books (name)
  SELECT name from normalized_data
  WHERE book_id IS NULL
  RETURNING id, name
)
-- insert a record into users_books for new books
INSERT INTO users_books (user_id, book_id, stock)
SELECT user_id, id, stock
FROM insert_new_book
JOIN normalized_data ON normalized_data.name = insert_new_book.name;