CODEWITHSUNDEEP
sqlpostgresqljoinmany-to-manylimit
ma11hew28
ma11hew28

Reputation: 126487

Get the latest messages between many users

In my app, users

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    username varchar(50) NOT NULL
);

can send messages

CREATE TABLE messages (
    id bigserial PRIMARY KEY,
    from_id bigint NOT NULL REFERENCES users ON DELETE RESTRICT,
    body text NOT NULL CHECK (body <> ''),
    created_at timestamp(0) NOT NULL DEFAULT LOCALTIMESTAMP(0)
);

to many recipients

CREATE TABLE message_recipients (
    message_id bigint NOT NULL REFERENCES messages ON DELETE CASCADE,
    user_id bigint NOT NULL REFERENCES users ON DELETE RESTRICT,
    PRIMARY KEY (message_id, user_id)
);

See this SQL Fiddle.

How do I SELECT the (e.g., 20) latest messages between a group of users?

E.g., how do I get the 20 latest messages between users 1, 2, and 3? I.e., how do I get the messages limited to the latest 20 that 1 has sent to both 2 & 3, 2 has sent to both 1 & 3, and 3 has sent to both 1 & 2?

NOTE: I don't want to assign the messages to channels. I want to filter them by senders & receivers.

Related Question: Get a subset of messages between specific users

Upvotes: 0

Views: 41

Answers (1)

teppic
teppic

Reputation: 7286

First aggregate the participants for each message, then you can match the participant list.

WITH target_group (members) AS (
  VALUES (ARRAY [1, 2, 3] :: BIGINT [])
), message_group AS (
    SELECT
      -- message id
      m.id,
      -- aggregate the ids of all message parties
      array_agg(mr.user_id) || m.from_id AS members
    FROM message_recipients mr
      JOIN messages m ON m.id = mr.message_id
      JOIN target_group tg ON tg.members @> ARRAY [m.from_id]
    GROUP BY m.id
)
SELECT
  m.id,
  m.body
FROM message_group mg
  JOIN messages m ON m.id = mg.id
  JOIN target_group tg ON tg.members @> mg.members AND array_length(tg.members, 1) = array_length(mg.members, 1)
ORDER BY m.id DESC
LIMIT 20;

Upvotes: 1

Related Questions