In postgres, what's the best way to AND do with a join table?
I have different user categories and a connection table that allows users to be in multiple categories. My join table is named category_users and consists of user_id and category_id.
I want to filter for users who are in category 1 and category2. For example, I want to find everyone who is interested in both baseball and football.
What's the best way to do this in PostgreSQL? I have the following workers:
select * from users
where users.id IN
( Select categories_users.user_id from categories_users
JOIN categories ON categories.id = categories_users.category_id
where categories.id = 1 OR categories.parent_id = 1)
AND users.id IN
(Select categories_users.user_id from categories_users
JOIN categories ON categories.id = categories_users.category_id
where categories.id = 2 OR categories.parent_id = 2)
However, this seems clunky and I'm wondering if there is a better way to do this. I've tried various joins but always end up looking for rows in the category_users table that have a category_id of 1 and 2, which is not possible.
Edit, I actually need to search in the parent category as well, so I modified the above query to include parent_id
source to share
You can also use COUNT (*) above a section to see how many categories a user has in the set of searched categories.
I created the following sample to see how this can be defined and parameterized. I created a function test.find_users_in_categories(BIGINT[])
that accepts an array of categories for which we need a list of users. Thus, the function will return all users in all given categories.
Solution for - search for users in all specified categories
CREATE SCHEMA test;
CREATE TABLE test.categories_users (
category_id BIGINT NOT NULL,
user_id BIGINT NOT NULL
);
INSERT INTO test.categories_users
(user_id, category_id)
VALUES
(33, 103),
(34, 104),
(35, 105),
(37, 105),
(35, 106),
(37, 106);
CREATE OR REPLACE FUNCTION test.find_users_in_categories(BIGINT[])
RETURNS TABLE (
user_id BIGINT
)
AS
$$
DECLARE
categories ALIAS FOR $1;
BEGIN
RETURN QUERY
SELECT t.user_id
FROM
(
SELECT
cu.user_id,
cu.category_id,
COUNT(*) OVER (PARTITION BY cu.user_id ) AS cnt
FROM test.categories_users AS cu
WHERE cu.category_id = ANY(categories)
) AS t
WHERE t.cnt = array_length(categories, 1)
GROUP BY t.user_id;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM test.find_users_in_categories(ARRAY[105, 106]);
DROP SCHEMA test CASCADE;
EDIT - [recursive solution]
Solution for - search for users in all specified categories and subcategories
See the following code for implementing a solution using JOIN + recursive CTE. I used JOIN instead of COUNT () because it looks better for this case.
CREATE SCHEMA test;
CREATE TABLE test.categories (
category_id BIGINT PRIMARY KEY,
parent_id BIGINT REFERENCES test.categories(category_id)
);
CREATE TABLE test.categories_users (
category_id BIGINT NOT NULL REFERENCES test.categories(category_id),
user_id BIGINT NOT NULL
);
INSERT INTO test.categories
(category_id, parent_id)
VALUES
(100, NULL),
(101, 100),
(102, 100),
(103, 101),
(104, 101),
(105, 101),
(106, NULL);
INSERT INTO test.categories_users
(user_id, category_id)
VALUES
(33, 103),
(34, 104),
(35, 105),
(37, 105),
(35, 106),
(37, 106);
CREATE OR REPLACE FUNCTION test.find_users_in_categories(BIGINT[])
RETURNS TABLE (
user_id BIGINT
)
AS
$$
DECLARE
main_categories ALIAS FOR $1;
BEGIN
RETURN QUERY
WITH
-- get all main categories and subcategories
RECURSIVE cte_categories (category_id, main_category_id) AS
(
SELECT cat.category_id, cat.category_id AS main_category_id
FROM test.categories AS cat
WHERE cat.category_id = ANY(main_categories)
UNION ALL
SELECT cat.category_id, cte.main_category_id
FROM cte_categories AS cte
INNER JOIN test.categories AS cat
ON cte.category_id = cat.parent_id
),
-- filter main categories that are found as children of other categories
cte_categories_unique AS
(
SELECT cte.*
FROM cte_categories AS cte
LEFT JOIN
(
SELECT category_id
FROM cte_categories
WHERE category_id <> main_category_id
GROUP BY category_id
) AS to_exclude
ON cte.main_category_id = to_exclude.category_id
WHERE to_exclude.category_id IS NULL
),
-- compute the count of main categories
cte_main_categories_count AS
(
SELECT COUNT(DISTINCT main_category_id) AS cnt
FROM cte_categories_unique
)
SELECT t.user_id
FROM
(
-- get the users which are found in each category/sub-category then group them under the main category
SELECT
cu.user_id,
cte.main_category_id
FROM test.categories_users AS cu
INNER JOIN cte_categories_unique AS cte
ON cu.category_id = cte.category_id
GROUP BY cu.user_id, cte.main_category_id
) AS t
GROUP BY t.user_id
-- filter users that do not have a match on all main categories or their sub-categories
HAVING COUNT(*) = (SELECT cnt FROM cte_main_categories_count);
END;
$$
LANGUAGE plpgsql;
SELECT * FROM test.find_users_in_categories(ARRAY[101, 106]);
DROP SCHEMA test CASCADE;
source to share