I've DSE Search enabled on a cluster and have a Cassandra table with search-index.
There is a requirement to support a non-primary-key based search from application. But the search is based upon the columns which are part of search-index. And I need to return the specific page of a paginated search result, along with total number of items.
To be specific, for a given search-name, page-number and page-size
I need to fetch just the page-size rows, with total-count
For example,
if there is a table called items having a column item-name.
And item-name is part of the search index
if there are 500 records where item-name=mps then when application queries with page-number=2 andpage-size=20 I need to return the items from 21 to 40 and somehow should also able to tell that total items are 500
I couldn't figure out how to do this in one query.
I can run same criteria twice, once for items and second time for count.
But this is dynamic data. So 2 different queries might create data inconsistency .
This is the type of query I'm using to fetch the items.
SELECT partition-key, clusterkey1, name, time-column
FROM keyspace-name.items
WHERE
partition-key = `ABCD`
AND solt_query ='{"q":"name:mps", "sort":"time-column DESC ","start":"21"'
LIMIT 20
For counts, replacing the select with count(1). Any directions are welcome but there is slim chance to change the database model, I mean I'm not allowed to create another table to maintain the counts.
