Cassandra CQL - clustering order with multiple clustering columns

Question

I have a column family with primary key definition like this:

...
PRIMARY KEY ((website_id, item_id), user_id, date)

which will be queried 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'd like to keep my column family ordered by date only, such as SELECT date FROM myCF ; would return the most recent inserted date.

Due to the order of clustering columns, what I get is an order per user_id then per date. If I change the primary key definition to:

PRIMARY KEY ((website_id, item_id), date, user_id)

I can no longer run the same query, as date must be restricted is user_id is.

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 doesn't seem to exist. Worst, maybe this is completely stupid and I don't get why.

Is there any ways of achieving this? Am I missing something?

Many thanks!

Answer 1

Your query example restricts user_id so that should work with the second table format. But if you are actually trying to run queries like

SELECT * FROM myCF
WHERE website_id = 30 AND item_id = 10
AND date > 'some_date'

Then you need an additional table which is created to handle those queries, it would only order on Date and not on user id

Create Table LookupByDate ... PRIMARY KEY ((website_id, item_id), date)

Answer 2

In addition to your primary query, if all you try to get is "return the most recent inserted date", you may not need an additional table. You can use "static column" to store the last update time per partition. CASSANDRA-6561