Taken from Physical Data Model https://academy.datastax.com/units/22018-physical-data-modeling-practical-application-data-modeling-apache-cassandra
CREATE TABLE actors_by_video ( video_id TIMEUUID, actor_name TEXT, character_name TEXT, PRIMARY KEY ((video_id), actor_name, character_name) );
Attempt to answer the question: Who was the actor that played the character Desi Collings?
SELECT * FROM actors_by_video WHERE video_id = 8a657435-0ef2-11e5-91b1-8438355b7e3a AND character_name = 'Desi Collings'; InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "character_name" cannot be restricted as preceding column "actor_name" is not restricted
What is the reason such a request be considered invalid?
I get it that because we filter on the 2nd sort key skipping the 1st sort key. But overall, the worst thing that could happen is to scan the block of rows of ONE single partition key. Which is still far better that the table scan allowed by ALLOW FILTERING.
A client side solution would do exactly that, bring back the whole block of rows and filter one by one until there is a match on the 2nd clustering key.
Can you please suggest an elegant CQL way to solve this problem?