卡桑德拉按时间事务表进行分页

Question

我有以下表/列族Cassandra来存储按时间(后代)排序的事务，其中每个事务属于一个帐户:

CREATE TABLE transactions (
    transaction_id uuid,
    description text,
    account_id uuid,
    category text,
    sub_category text,
    date timestamp,
    amount bigint,
    PRIMARY KEY (account_id, date, transaction_id, amount)
) WITH CLUSTERING ORDER BY (date DESC);

我希望在事务中分页，为特定帐户按日期排序的25个事务获取组。

这个定义是可能的，还是我需要其他额外的CF来帮助这个功能?你有什么建议?

Answer 1

所以我不确定为什么你的PK中有数量，但现在这应该无关紧要。

对于给定的帐户，您可以按日期排序的时间获取25个结果。Datastax Native驱动程序将允许您通过分页选项执行此操作。

//Expected inputs account_id and if not the first page a state 
//string from the last page of data
Select query = select().from('keyspace', 'transactions')
.where(eq('account_id', account_id))
.orderBy(desc('date'))
if (state) {
    query.setPagingState(PagingState.fromString(state))
}
query.setFetchSize(25)
ResultSet resultSet = session.execute(query)    
String newState = resultSet.getExecutionInfo().pagingState?.toString()
//This will get you just the 25 rows you requested first
resultSet.getAvailableWithoutFetching()
//When sending data back to the page you will need to make sure you also 
//send back the paging state so it can help you go to the next 25.