Postgres: сложный запрос на множественное обновление для отношения "один ко многим"

Моя база данных выглядит следующим образом: https://dbfiddle4ec5e3d1e0e0e0e6e0e08e08e08e0e0e0e0e0e0e0e0e0e6e08e0e0e0e0e0e6

So

  • У пользователя может быть несколько книг
  • Основные отношения "один ко многим"

Предположим, что если пользователь обновит уже существующую книгу и добавит новые книги, как я могу сделать для этого запрос?

Я провел небольшое исследование и пришел к выводу, что на помощь мне придут либо cte, либо функция.

Данные моего тела запроса (JSON) FOR PATCH QUERY будут выглядеть следующим образом

{
   // 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);

Итак, глядя на структуру выше, мне нужно, чтобы таблица books_users обновилась соответствующим образом.

Мое текущее понимание - передать объект книг как jsonb в функцию postgresql. Затем прокрутите его, обновляя / добавляя книги соответственно. Я не уверен, как мне узнать, есть ли у пользователя книга или нет.

Как бы вы, ребята, преобразовали это тело запроса в вышеупомянутый сложный запрос на обновление? И будет ли выполнение этого в функции транзакционным?


postgresql node.js plpgsql pg
person Élisa Plessis    schedule 30.08.2019    source источник
comment
Значит, users_books.user_id И users_books.book_id уникально объединены?   -  person madflow    schedule 30.08.2019
comment
@madflow да. Но мне нужно поддерживать отношения один со многими   -  person Élisa Plessis    schedule 30.08.2019

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете сделать все это в одном операторе, но было бы лучше иметь некоторые уникальные ограничения, чтобы он не сделал что-то плохое. users_books (book_id, user_id) должны быть уникальными, а книги (name) должны быть уникальными.

Вот скрипка

Вот важная часть:

-- 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;
person Jeremy    schedule 30.08.2019
comment
Я предполагаю, что создание уникальной книги и идентификатора пользователя может нарушить отношения "один ко многим", верно? - person Élisa Plessis; 30.08.2019
comment
Нет, это просто потребует, чтобы каждый пользователь мог иметь только одну запись в книге. Не делайте book_id и user_id уникальными, используйте комбинацию (book_id, user_id). Вероятно, стоит также сделать их не равными нулю. - person Jeremy; 30.08.2019
comment
Работает отлично @jeremy Какую книгу вы бы порекомендовали, чтобы лучше понять postgresql? - person Élisa Plessis; 31.08.2019