By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE "conversationUsers"(
"conversationId" int
, "userId" int
, PRIMARY KEY ("userId", "conversationId")
);
INSERT INTO "conversationUsers" VALUES
(1, 1), (1,4), (1,6)
, (2, 1), (2,4), (2,6)
, (3, 1), (3,4), (3,6), (3,7) -- additional user
, (4, 1), (4,6) -- missing user
, (5, 7), (5,9) -- unrelated
;
14 rows affected
SELECT "conversationId"
FROM "conversationUsers" c
WHERE "userId" = ANY ('{1,4,6}'::int[])
GROUP BY 1
HAVING count(*) = array_length('{1,4,6}'::int[], 1)
AND NOT EXISTS (
SELECT FROM "conversationUsers"
WHERE "conversationId" = c."conversationId"
AND "userId" <> ALL('{1,4,6}'::int[])
);
| conversationId |
|---|
| 1 |
| 2 |
WITH RECURSIVE rcte AS (
SELECT "conversationId", 1 AS idx
FROM "conversationUsers"
WHERE "userId" = ('{1,4,6}'::int[])[1]
UNION ALL
SELECT c."conversationId", r.idx + 1
FROM rcte r
JOIN "conversationUsers" c USING ("conversationId")
WHERE c."userId" = ('{1,4,6}'::int[])[idx + 1]
)
SELECT "conversationId"
FROM rcte r
WHERE idx = array_length(('{1,4,6}'::int[]), 1)
AND NOT EXISTS (
SELECT FROM "conversationUsers"
WHERE "conversationId" = r."conversationId"
AND "userId" <> ALL('{1,4,6}'::int[])
);
| conversationId |
|---|
| 1 |
| 2 |
CREATE OR REPLACE FUNCTION f_conversations(VARIADIC _user_ids int[])
RETURNS SETOF int AS
$func$
WITH RECURSIVE rcte AS (
SELECT "conversationId", 1 AS idx
FROM "conversationUsers"
WHERE "userId" = $1[1]
UNION ALL
SELECT c."conversationId", r.idx + 1
FROM rcte r
JOIN "conversationUsers" c USING ("conversationId")
WHERE c."userId" = $1[idx + 1]
)
SELECT "conversationId"
FROM rcte r
WHERE idx = array_length($1, 1)
AND NOT EXISTS (
SELECT FROM "conversationUsers"
WHERE "conversationId" = r."conversationId"
AND "userId" <> ALL($1)
)
$func$ LANGUAGE sql;
COMMENT ON FUNCTION f_conversations(int[]) IS $$Get conversations with all users in the given array and no other.
Call:
SELECT * FROM f_converstaions(1,4,6);
SELECT * FROM f_converstaions(VARIADIC '{1,4,6}'); --$$;
SELECT * FROM f_conversations(1,4,6);
| f_conversations |
|---|
| 1 |
| 2 |
PREPARE conversations(int[]) AS
WITH RECURSIVE rcte AS (
SELECT "conversationId", 1 AS idx
FROM "conversationUsers"
WHERE "userId" = $1[1]
UNION ALL
SELECT c."conversationId", r.idx + 1
FROM rcte r
JOIN "conversationUsers" c USING ("conversationId")
WHERE c."userId" = $1[idx + 1]
)
SELECT "conversationId"
FROM rcte r
WHERE idx = array_length($1, 1)
AND NOT EXISTS (
SELECT FROM "conversationUsers"
WHERE "conversationId" = r."conversationId"
AND "userId" <> ALL($1)
);
EXECUTE conversations('{1,4,6}');
| conversationId |
|---|
| 1 |
| 2 |