CODEWITHSUNDEEP
javacassandracql
Jaya Ananthram
Jaya Ananthram

Reputation: 3463

Cassandra CQL paging for Composite key

Is there is any easy way to query wide row by paging. My schema is,

CREATE TABLE USER_LIKED_ITEMS (
user_id text,
event_id text,
item_id text,
description text,
PRIMARY KEY(user_id, event_id, item_id)
);

I want to get all the rows for the given user_id (consider there are 500000 rows for a given user_id with various combination of event_id and item_id) through paging with a count 25. The query I have tried is select * from USER_LIKED_ITEMS where user_id = '101' LIMIT 25; Once results is arrived then 25th record has a value 

user_id = 101,
event_id = 25,
item_id = 25,
description = XXXXX

My next query is, select * from USER_LIKED_ITEMS where user_id = '101' and event_id = '25' and item_id > '25' LIMIT 25; Retrieves the results of 10 records having the last record value like, 

user_id = 101,
event_id = 25,
item_id = 35,
description = XXXXX

So again I need to frame select * from USER_LIKED_ITEMS where user_id = '101' and event_id > '25'. So It is quite tricky and difficult to retrieve all the results by framing different query to retrieve all the results. Is there is any easy way to get all the record for the given user_id with pagination count of 25? I am using cassandra-1.2.15 and cassandra-driver-core-1.0.3.jar. Cassandra2.0 has paging support, but I am not allowed to upgrade cassandra version.

Upvotes: 2

Views: 409

Answers (1)

Aaron
Aaron

Reputation: 57748

There is a way in 1.2 to "page" through your results with the token() function and based on your partitioning key (which for you is user_id). But I suspect if there was any value (to you) to query your results by just user_id, you would be doing it.

One solution that might be of some benefit to you, is to duplicate your data into a query table to solve this:

CREATE TABLE USER_LIKED_ITEMS_BY_PAGENO (
user_id text,
event_id text,
item_id text,
description text,
pageno bigint,
PRIMARY KEY(user_id, pageno, event_id, item_id)
);

Of course this is assuming that you know at insert-time which rows belong on which page (maybe for every 25 inserted, you increment pageno, or something similar). But if you did, you could put them into a table like that, and then SELECT from it like this:

select * from USER_LIKED_ITEMS_BY_PAGENO 
where user_id = '101' and pageno = 4;

Upvotes: 1

Related Questions