I have 2 tables:

collections_books (collection_id, book_id)
users_collections (user_id, collection_id, access)

I'm using PostgreSQL.

The following query gives me a list of book_ids grouped by collection_ids. The problem is, since I'm using where condition, the results are limited to only the collections allowed for user_id = 3.

But, I want all the collection_ids and the corresponding book_ids

1. As an array, if the user_id has access = allow
2. As an empty array, if the user_id is not present in the users_collections or user_id != allow

SELECT c.collection_id, ARRAY_AGG(c.book_id)
FROM collections_books AS c
LEFT JOIN users_collections AS u
ON c.collection_id = u.collection_id
WHERE 
  u.access = 'allow' AND
  u.user_id = 3
GROUP BY c.collection_id;

CodePudding user response：

You check array_length(). If it's less then 1 then return a array with of value {null}

If you want to get all the collection_id from collections_books but array of book_id only if u.access = 'allow' AND u.user_id = 4 other wise null then use below query:

SELECT c.collection_id, 
    (
        CASE 
             WHEN max(u.access) = 'allow' AND max(u.user_id) = 4
             THEN ARRAY_AGG(c.book_id)
             ELSE '{null}'::int[] 
        END
    )
    FROM collections_books AS c
    LEFT JOIN users_collections AS u
    ON c.collection_id = u.collection_id
    GROUP BY c.collection_id;

CodePudding user response：

Please check out below answer and let me know whether it returns your desired output or not:

Schema and insert statements:

     create table users_collections (user_id int, collection_id int, access varchar(20));
     insert into users_collections values(3, 1, 'allow');
     insert into users_collections values(3, 2, 'allow');
     insert into users_collections values(4, 3, 'allow');
     insert into users_collections values(3, 5, 'not allow');

 
     create table collections_books (collection_id int, book_id int);
     insert into collections_books values(2,24);
     insert into collections_books values(3,35);
     insert into collections_books values(3,25);
     insert into collections_books values(1,36);
     insert into collections_books values(1,22);
     insert into collections_books values(1,24);
     insert into collections_books values(2,34);
     insert into collections_books values(5,344);
     insert into collections_books values(6,474);

Query:

     SELECT c.collection_id, (CASE WHEN max(u.access) = 'allow' AND max(u.user_id) = 3
     THEN ARRAY_AGG(c.book_id)
     ELSE '{null}'::int[] END)
     FROM collections_books AS c
     LEFT JOIN users_collections AS u
     ON c.collection_id = u.collection_id
     GROUP BY c.collection_id;
 

Output:

 |collection_id | case      |
 |------------: | :---------|
 |            3 | {35,25}   |
 |            5 | {NULL}    |
 |            6 | {NULL}    |
 |            2 | {24,34}   |
 |            1 | {36,24,22}|

db<fiddle here