Establishing Relationships - Learning Apache Cassandra - page 108

The form of the single table
To demonstrate this approach, we'll start by creating a new table, user_follows , that
has an identical structure to our user_inbound_follows table:
CREATE TABLE "user_follows" (
"followed_username" text,
"follower_username" text,
PRIMARY KEY ("followed_username", "follower_username")
);
As with user_outbound_follows , this new table uses a followed user as its partition
key, and the follower as its clustering column. At a higher level, for each user, U , this table
stores a partition of rows representing the users that follow U . So, if we'd like to find out
who follows a given user, we can do so very efficiently using this table.
However, we've said that we want to use the same table for both in bound and out bound
follows; that is to say, we'd like this table to be able to answer the question, "Who does
Alice follow?" Let's recall what happens if we naively try a query to answer that question
on the user_follows table:
SELECT * FROM "user_follows"
WHERE "follower_username" = 'alice';
Of course, Cassandra won't allow us to perform this query:
The structure of Cassandra's primary keys requires us to strictly adhere to a set of rules for
what columns we may specify in the WHERE clause of a query. In particular, if we want to
look up rows by a given column or columns, we can use:
• Exact values for all partition key columns
• Exact values for all partition key columns, and exact value for the first clustering
column