Get the NOT IN values into an array in postgres SQL-CodePudding

I am looking to obtain a set of results where I receive a user_id and a collection of books that the user HAS NOT reviewed. I would like the following as the required output:

user_id	Array of books not reviewed
1	{Array of books not reviewed}
2	{Array of books not reviewed}.

Currently there are two tables that this query would require to be pulled from. One contains "reviews" that possesses the user_id of the review, and the book_id that the user HAS reviewed. The book_id is a foreign key to the the "books" table where the total list of books is contained. Therefore for each user_id in the output, I would require the total list of books, excluding the ones that have been reviewed per user.

See the insert statements below for the reviews and books tables:

Reviews tables -

CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    rating INT CHECK (rating BETWEEN 0 AND 10),
    review_text TEXT,
    book_id INT REFERENCES books(id),
    user_id INT
);

Example of a snippet of the table data:

review_id	rating	book_id.	user_id
1	8	2	1
2	6	3	3

Books table -

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    price_in_pence INTEGER,
    quantity_in_stock INTEGER
);

Example of a snippet of books data:

id	title	price_in_pence	quantity_in_stock
1	bookname	549	12
2	LOTR	799	9

I have tried the following query, however this is not dynamic and only works per user_id entered (identified below using '**'):

SELECT r.user_id, 
       array( SELECT b.title 
              FROM books b 
              WHERE b.id NOT IN (SELECT r.book_id 
                                 FROM reviews r  
                                 WHERE user_id = '4')
               ) AS Books_not_reviewed 
FROM reviews r
GROUP BY (r.user_id);

Apologies I am somewhat new to SQL. Any help would be greatly appreciated. I am using Postgres version 14.1

CodePudding user response：

You can make your query dynamic, if you make the userid as a parameter and use prepared statements.

Also, the below query can give you the list of all books NOT reviewed by each user, you can modify the query to suit your needs.

select u.userid, array( select bookid from books 
where bookid not in (select 
           bookid from reviews r 
            where r.userid=u.userid) )
    from users u