Cassandra chat app: sorting rooms after last message inserted

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.

Solution

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!