Postgresql: resolve a one-to-many relationship and delete given tuples, all in one query

Question

I have a table as such:

CREATE TABLE users (id, ...)
CREATE TABLE groups (id, ...)
CREATE TABLE group_roles (group_id REFERENCES pages.id, role, ...)
CREATE TABLE user_memberships (
  user_id REFERENCES users.id,
  group_role_id REFERENCES group_roles.id,
)

I have a bunch of (user_id, group_id) tuples that I need to pass into Postgres, through an ORM (Sequelize), and then I need postgres to delete the user_memberships rows that correspond to the (user_id, group_id) pairs I passed in. I'd also like all of this to happen in one query.

I'd like to write the following code:

DELETE FROM user_memberships
WHERE (
  (
     user_id = :user_id_n
     AND group_role_id IN (
       SELECT id FROM group_roles WHERE group_id = :group_id_n
     )
  )
)

where :user_id_n and :group_id_n would be the nth elements of an array of (user_id, group_id) tuples. But this is not valid pgpsql. What would valid pgpsql look like?

Answer 1

Your table definitions are a bit off but there is nothing about the delete on its own that won't work:

CREATE TABLE users (id int primary key);
CREATE TABLE "groups" (id int primary key);
CREATE TABLE pages (id int primary key);
CREATE TABLE group_roles (
  id int PRIMARY KEY,
  group_id int REFERENCES "groups"(id));
CREATE TABLE user_memberships (
  user_id int REFERENCES users(id),
  group_role_id int REFERENCES group_roles(id)
);
insert into users values (1),(2);
insert into "groups" values (1),(2);
insert into group_roles values (1,1),(2,2);
insert into user_memberships values (1,1),(2,2);

That DELETE is perfectly valid, pretty much exactly how you typed it: demo at db<>fiddle

DELETE FROM user_memberships
WHERE user_id = 1
AND group_role_id IN 
  (    SELECT id 
       FROM group_roles 
       WHERE group_id = 1 );

It can work fine with the whole array in it, too:

DELETE FROM user_memberships
USING generate_series(1,array_length(ARRAY[[1,1],[2,2]],1))as a(n)
WHERE user_id = (ARRAY[[1,1],[2,2]])[n][1]
AND group_role_id IN 
  (    SELECT id 
       FROM group_roles 
       WHERE group_id = (ARRAY[[1,1],[2,2]])[n][2] )
RETURNING *;