Cassandra chat app: sorting rooms after last message inserted

Question

For a messaging app I have a database structure comparatively to:

CREATE TABLE users(
    userid text,
    name text, 
    rooms list<text>
    ...
    PRIMARY KEY (userid)
);

CREATE TABLE rooms(
    roomid text,
    members list<text>,
    createdat bigint,
    lastmessage bigint,
    ...
    PRIMARY KEY (roomid, createdat)
);

CREATE TABLE messages(
    roomid text,
    bucket int,
    messageid bigint,
    authorid text,
    ...
    PRIMARY KEY ((hash, roomid), messageid)
);

On startup the client requests all rooms for a given user. I expect at some point, that a user will be member of hundreds of channels. So I only want to retrieve the last X active channels to reduce traffic.

Currently the room stores the last messageid (snowflake including timestamp) so I am capable to sort, after retrieving all rooms.

What changes are necessary to only load the last X active rooms from Cassandra? I know that I need to denormalize the structure somehow, but I do not know how.

Answer 1

This looks like a variation of your question in #68782996 where I suggested creating this table for your app query "give me all rooms for a user":

CREATE TABLE rooms_by_userid (
  ...
  PRIMARY KEY (userid, roomid)
)

From your description, it sounds like the app query is "give me the 10 most recent rooms by a user". You also mentioned that you are determining the most recent rooms using the messageid. In this case, the table would look like:

CREATE TABLE rooms_by_userid_by_messageid (
   userid text,
   messageid bigint,
   roomid text,
   ...
   PRIMARY KEY (userid, messageid)
) WITH CLUSTERING ORDER BY (messageid DESC, roomid ASC)

The data in this table would be partitioned by user ID and would contain rows sorted by message ID in reverse order (most recent first) where each message has an associated rooms. You would retrieve the 10 most recent rooms using LIMIT 10 like this:

SELECT roomid FROM rooms_by_userid_by_messageid
  WHERE userid = ?
  AND messageid = ?
  LIMIT 10;

The important point here is that the data is already sorted in the order you need so you don't need to do any client-side sorting when you get the results from the database. Cheers!

Answer 2

Create another table

CREATE TABLE user_active_channels (
   userid text,
   time bigint,
   room text,
   PRIMARY KEY (userid, time))
WITH CLUSTERING ORDER BY (time DESC);

Each time a user joins a room , save the data in this table. Then query like:

SELECT room FROM user_active_channels
 WHERE userid='The users Id' AND time > 0 limit 5;

Since the clustering column is time, the data is ordered by descending order on the partition, so the first 5 records will be the records that have the largest time, hence the latest user's active room and to avoid retrieving all the data, you can limit the query to only return the first 5 records.