Cassandra Order By Updated At

Question

I'm trying to build a cassandra schema to represent chat. The one thing i can't seem to figure out is how to query most recently updated rooms (similar to most chat app list view)

• Fields desired in list view ordered by updated_at desc
  • *room id
  • room title
  • room image
  • *user
  • *updated_at
  • *message entry
  • *message type
  • *metadata

Current Tables

Create TYPE user(
   id uuid,
   name text,
   avatar text
);

CREATE TABLE rooms(
  id uuid,
  "name" text,
  image text,
  users set<user>,
  archived boolean,
  created_at timestampz,
  updated_at timestampz,
  PRIMARY KEY(id)
);


CREATE TABLE messages(
  room_id uuid,
  message_id timeuuid,
  user user,
  message_type int,
  entry text,
  metadata map<text, text>,
  PRIMARY KEY(room_id, message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);


CREATE TABLE rooms_by_user(
  user_id uuid,
  room_id uuid,
  PRIMARY KEY(user_id, room_id)
);

Possible solutions that i can come up with.

• Duplicate all room details to each message
  • allows easy query with SELECT * FROM messages PER PARTITION LIMIT 1
  • this would be a lot of duplicate data per message...
• Query latest messages which user belongs to get room ids then query rooms
  • This doesn't seem to be the cassandra way?

Is there a better way to model my data?

Answer 1

By looking at the schema it looks like you need relational database.

In Cassandra usually you use one table per query, it means you you should design your table by how you will structure query.

Also you can query by partition key or clustering column (second one should be partition key + clustering column). So in order to query by updater_at, you need to make that column as clustering column. And keep in mind that in Cassandra you cannot alter keys.