Cassandra how to query to get the most recent message

Question

Please i need a help am new to Cassandra. I have a table called conversation, user1 is my partition key while time is my clustering key

How can i query this table to get all users, user1 have established conversation with, including the most recent message.

CREATE TABLE conversation (
  user1 text,
  conversationId text,
  message text,
  user2,
  time,
  PRIMARY KEY(user1, time) 
) WITH CLUSTERING ORDER BY (time DESC)

.............................................................
| User1 | ConversationId |    Message    | User 2  |  Time  |
|.......|................|...............|.........|........|
| Bobby | 100 - 101      | Hello         | Chris   |12:10pm |
| Bobby | 100 - 101      | U there?      | Chris   |12:11pm |
| Bobby | 100 - 102      | Am here       | Dan     |12:12pm |
| Bobby | 100 - 102      | Hello Dan     | Dan     |12:13pm |
| Bobby | 100 - 103      | Am coming     | Sam     |12:14pm |
| Bobby | 100 - 103      | Hello sam     | Sam     |12:15pm |

This should be my output after query.

.............................................................
| User1 | ConversationId |    Message    | User 2  |  Time  |
|.......|................|...............|.........|........|
| Bobby | 100 - 103      | Hello sam     | Sam     |12:15pm |
| Bobby | 100 - 102      | Hello Dan     | Dan     |12:13pm |
| Bobby | 100 - 101      | U there?      | Chris   |12:11pm |

Answer 1

You cannot do that in Cassandra with your existing table. But you could create a new table for this purpose, and insert all records in that table too.

CREATE TABLE conversation_lastmessage (
  user1 text,
  conversationId text,
  message text,
  user2 text,
  time timestamp,
  PRIMARY KEY ((user1), user2));

Assuming that you insert your data in order of time, you will get the latest messages of user1 with every correspondent.

SELECT * FROM conversation_lastmessage WHERE user1 = 'user1';

Answer 2

In Cassandra you store your data ordered on disk so you don't use an order by clause when you select your data. Using @medvekoma table example you would add WITH CLUSTERING ORDER BY in the following manner. Keep in mind that you need to have the Time element in the Primary Key declaration and then you can use the Time element in the clustering order by statement.

CREATE TABLE conversation_lastmessage (
  user1 text,
  conversationId text,
  message text,
  user2 text,
  time timestamp,
  PRIMARY KEY ((user1), user2, time)
)
WITH CLUSTERING ORDER BY (time DESC);

So no matter what order your data is loaded it will be stored on disk in the order you need. Best Practice in Cassandra is to create a table for each individual query. To learn more about data modeling in Cassandra you should refer to the Datastax academy, register and take the Data Modeling course,