samedi 27 mai 2023

API getting multiple rows as array from database

I have 4 tables:

CREATE TABLE Store (
  id INTEGER PRIMARY KEY,
  name varchar(255) NOT NULL
);

CREATE TABLE Book (
  id uuid PRIMARY KEY,
  name varchar(255) NOT NULL
);

CREATE TABLE BookAvailableInStore (
  book_id uuid NOT NULL REFERENCES Book (id) ON DELETE CASCADE ON UPDATE CASCADE,
  store_id INTEGER NOT NULL REFERENCES Store (id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (book_id, store_id)
);

CREATE TABLE UserBookInterest (
  user_account_id uuid NOT NULL REFERENCES UserAccount (id) ON DELETE CASCADE ON UPDATE CASCADE,
  book_id uuid NOT NULL REFERENCES Book (id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (user_account_id, book_id)
);

Ultimately, I would like my API to return all books that are of interest to a given user, with the stores they can find them:

[
  {"book_id":1, "book_name":"My Book 1", "stores":[{"id":1, "name": "Store 1"}, {"id":2, "name": "Store 2"}]},
  {"book_id":2, "book_name":"My Book 2", "stores":[{"id":1, "name": "Store 1"}]},
  {"book_id":3, "book_name":"My Book 3", "stores":[]},
]

I am not sure what is the recommended clean approach to get such a result:

  1. Should I write a single SQL query that returns all the books/store availability
book_id, book_name, store_id, store_name
1, "book 1", 1, "store 1"
1, "book 1", 2, "store 2"
2, "book 2", 1, "store 1"
3, "book 3", null, null

and then loop through all rows and "merge" the stores for each book?

  1. Or should I get the list of books of interest for the user, and then for each book have a new query populating the "stores" information? (it would mean that if the user has 300 books of interest, it would run 301 queries to the DB)?
  2. Or another better way?

If it is relevant, I am using NodeJS and Postgres.

Aucun commentaire:

Enregistrer un commentaire