How to get row where both values could be in two columns?-CodePudding

I have a table

-- Table Definition ----------------------------------------------

CREATE TABLE rooms (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id_one uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    user_id_two uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    create_time timestamp with time zone NOT NULL DEFAULT now()
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX room_pkey ON rooms(id int4_ops);

I want get the rooms.id with a specific pair of user_ids.

What's the best way to about doing this?

SELECT r.id
FROM rooms r
WHERE '9af0521d-f999-42e8-aafd-4bf2d839eafb' in (user_id_one, user_id_two)
  AND '0ff77b22-62f9-44ce-8f2e-7e85726dbb3e' in (user_id_one, user_id_two)

SELECT r.id
FROM rooms r
WHERE user_id_one in ('9af0521d-f999-42e8-aafd-4bf2d839eafb', '0ff77b22-62f9-44ce-8f2e-7e85726dbb3e')
  AND user_id_two in ('9af0521d-f999-42e8-aafd-4bf2d839eafb', '0ff77b22-62f9-44ce-8f2e-7e85726dbb3e')

SELECT r.id
FROM rooms r
WHERE (
              (user_id_one = '9af0521d-f999-42e8-aafd-4bf2d839eafb'
                  AND user_id_two = '0ff77b22-62f9-44ce-8f2e-7e85726dbb3e')
              OR
              (user_id_one = '0ff77b22-62f9-44ce-8f2e-7e85726dbb3e'
                  AND user_id_two = '9af0521d-f999-42e8-aafd-4bf2d839eafb')
          )

Or something completely different?

CodePudding user response：

Assuming the two user IDs can never have the same value, all three of your versions are valid. I can suggest another version, perhaps more terse than the ones you already have:

SELECT id
FROM rooms r
WHERE LEAST(user_id_one, user_id_two)    = '0ff77b22-62f9-44ce-8f2e-7e85726dbb3e' AND
      GREATEST(user_id_one, user_id_two) = '9af0521d-f999-42e8-aafd-4bf2d839eafb';

The above least/greatest approach is a trick which kills two birds with one stone. Regardless of in which order the matching set of IDs appear, the above works, by asserting that the smaller UUID appear as one user ID and the larger UID as the other user ID.

CodePudding user response：

You can compare via arrays:

WHERE ARRAY [user_id_one,user_id_two] @> ARRAY ['9af0521d-f999-42e8-aafd-4bf2d839eafb','0ff77b22-62f9-44ce-8f2e-7e85726dbb3e']

here's dbfiddle example

CodePudding user response：

I would change your 2nd query so that you pass the pair of uuids that you want only once:

SELECT id
FROM rooms 
WHERE (?, ?) IN ((user_id_one, user_id_two), (user_id_two, user_id_one));