I have a family of columns with a primary key definition:
...
PRIMARY KEY ((website_id, item_id), user_id, date)
which will be requested using queries such as:
SELECT * FROM myCF
WHERE website_id = 30 AND item_id = 10
AND user_id = 0 AND date > 'some_date' ;
However, I want my column family to be ordered only by date, for example SELECT date FROM myCF ;returning the most recently inserted date.
Due to the clustering order of the columns, I get the order for user_id and then for the date. If I changed the definition of the primary key to:
PRIMARY KEY ((website_id, item_id), date, user_id)
I can no longer run the same request, since the date should be limited to user_id.
I thought there might be some way to say:
...
PRIMARY KEY ((website_id, shop_id), store_id, date)
) WITH CLUSTERING ORDER BY (store_id RANDOMPLEASE, date DESC) ;
But it does not seem to exist. Worse, maybe this is completely stupid, and I don’t understand why.
Are there any ways to achieve this? Did I miss something?
Many thanks!