Postgres tags list and tag filtering-CodePudding

I have 3 tables

users table:
     --------- --------- 
    | id      |  name   |
     --------- --------- 
    | 1       | James   |
    | 2       | Carl    |
    | 3       | Bob     |
    | 4       | Steve   |
    | 5       | Evan    |
     --------- --------- 

tags table:
     --------- --------- 
    | id      | name    |
     --------- --------- 
    | 1       | Travel  |
    | 2       | Food    |
    | 3       | Fitness |
     --------- --------- 

usertag table:
     ---------- ---------- 
    | user_id  |  tag_id  |
     ---------- ---------- 
    | 1        | 1        |
    | 2        | 2        |
    | 1        | 3        |
    | 3        | 2        |
    | 3        | 1        |
    | 4        | 3        |
    | 5        | 1        |
     ---------- ----------

I made a query that fetches all the users and their tags in a comma-separated field

SELECT users.*, ARRAY_AGG(usertag.tag_id) as tags
FROM users
JOIN usertags on users.id = usertag.user_id
GROUP BY users.id

Which gives me the result:

     --------- --------- ---------- 
    | id      |  name   | tags     |
     --------- --------- ---------- 
    | 1       | James   | [1,3]    |
    | 2       | Carl    | [2]      |
    | 3       | Bob     | [2,1]    |
    | 4       | Steve   | [3]      |
    | 5       | Evan    | [1]      |
     --------- --------- ----------

What I need to do next is filter by tags. Very much an all-or-nothing type of filtering. So if I want a user with tags of 1 and 3, I should only get back James. Even though Steve, Bob, and Evan have tags 1 or 3, only James has the combination of 1 and 3.

I tried the following but it doesn't give me the results I expect

SELECT users.*, ARRAY_AGG(usertags.tag_id) as tags
FROM users
JOIN usertags on users.id = usertags.user_id
WHERE usertags.tag_id IN (1,3)
GROUP BY users.id

But I get this back

     --------- --------- ---------- 
    | id      |  name   | tags     |
     --------- --------- ---------- 
    | 1       | James   | [3,1]    |
    | 3       | Bob     | [1]      |
    | 4       | Steve   | [3]      |
    | 5       | Evan    | [1]      |
     --------- --------- ----------

When I would really just like to get the users that have all those tags. In this case, it would be one user, James.

     --------- --------- ---------- 
    | id      |  name   | tags     |
     --------- --------- ---------- 
    | 1       | James   | [3,1]    |
     --------- --------- ----------

How can I change this query to make it an all-or-nothing type of filtering where I pass in any number of tag ids?

CodePudding user response：

Assuming you want users having tags 1 and 3, and only those tags, you may use:

SELECT u.*, ARRAY_AGG(ut.tag_id) AS tags
FROM users u
INNER JOIN usertags ut ON u.id = ut.user_id
GROUP BY u.id
HAVING MIN(ut.tag_id) <> MAX(ut.tag_id) AND
       COUNT(*) FILTER (WHERE ut.tag_id NOT IN (1, 3)) = 0;

CodePudding user response：

If you want the users you have at least those two tags, you can use the overlaps operator &&

SELECT u.*, ARRAY_AGG(ut.tag_id) AS tags
FROM users u
  JOIN usertags ut ON u.id = ut.user_id
GROUP BY u.id
HAVING ARRAY_AGG(ut.tag_id) && array[1,3];

If you need those users that have exactly those two tag (not more), then you can either an equality condition:

HAVING ARRAY_AGG(ut.tag_id order by ut.tag_id) = array[1,3];

Note the order by to make the order in both arrays identical. Alternatively you can combine the @> and <@ operators

HAVING ARRAY_AGG(ut.tag_id) @> array[1,3]
   AND ARRAY_AGG(ut.tag_id) <@ array[1,3]