Cassandra + Fetch the last records using in query

Question

I am new in this cassandra database using with nodejs.

I have user_activity table. In this table data will insert based on user activity.

Also I have some user list. I need to fetch the data in that particular users and last record.

I don't interest to put the query in for loop. Have any other idea to achieve this?

Example Code:

var userlist = ["12", "34", "56"];
var query = 'SELECT * FROM user_activity WHERE userid IN ?';
server.user.execute(query, [userlist], {
    prepare : true
}, function(err, result) {
   console.log(results);
});

How to get the user lists for last one ?

Example:

user id = 12 - need to get last record;
user id = 34 - need to get last record;
user id = 56 - need to get last record;

I need to get these 3 records. Table Schema:

CREATE TABLE test.user_activity (
    userid text,
    ts timestamp,
    clientid text,
    clientip text,
    status text,
    PRIMARY KEY (userid, ts)
)

@abdulbarik - I want "12", "34", "56" this users last row details — RSKMR, Sep 30 '16 at 07:29

score 2 · Answer 1 · edited Sep 30 '16 at 13:34

2

It is not possible if you use the IN filter.

If it is a single user_id filter you can apply order by. Of course you need a column for inserted/updated time. So query will be like this:

SELECT * FROM user_activity WHERE user_id = 12 ORDER BY updated_at LIMIT 1;

edited Sep 30 '16 at 13:34

Aaron

55,518
11
116
132

answered Sep 30 '16 at 07:28

Daniel A

41
5

Its not a single. need to get multiple users last records. – RSKMR Sep 30 '16 at 07:30
then it is not possible in query. You must handle something in code level – Daniel A Sep 30 '16 at 07:31
It's possible to fetch the data using executeBatch ? ( I mean multiple select query in batch) – RSKMR Sep 30 '16 at 11:06
It looks like he does have a column for the time...`ts`. And with your `ORDER BY` I assume you mean to have `DESC` on the end? – Aaron Sep 30 '16 at 13:35
@RSKMR Its not possible even you user executeBatch in this case. – Daniel A Oct 03 '16 at 12:09
@Aaron ts is the time to order the row and it should have DESC at the end. I just thrown a random query. – Daniel A Oct 03 '16 at 12:11

score 1 · Answer 2 · answered Sep 30 '16 at 07:31

1

You can put N value to get number of records

SELECT * FROM user_activity WHERE userid IN ? ORDER BY id DESC LIMIT N

answered Sep 30 '16 at 07:31

abdulbarik

6,101
5
38
59

I am expecting results to get each users last records. – RSKMR Sep 30 '16 at 07:33
my query does to get 3 records in your case if put it `3` and that will get last three records of that user which you have mentioned.Isn't? – abdulbarik Sep 30 '16 at 07:50
No. Sorry if my content is clear means. My question is need to get those 3 users (each and everyone) results of last records – RSKMR Sep 30 '16 at 08:28

Cassandra + Fetch the last records using in query

2 Answers2

Linked