Database Reference
In-Depth Information
Looking up follow relationships
Now that we've studiously designed our follow tables to efficiently support our applica-
tion's data access patterns, let's do some data access. To start, we'll want to give
alice
an
interface to manage the list of users she follows; this interface will, of course, need to show
her who she currently follows:
SELECT "followed_username"
FROM "user_outbound_follows"
WHERE "follower_username" = 'alice';
Here we ask for all of the outbound follows in the partition of
alice
: an efficient query,
since it only looks up a single partition's worth of data. As expected, we see that
alice
follows
bob
and
carol
:
Note that the usernames returned are in alphabetical order: this is not a coincidence. Since
followed_username
is the clustering column in the
user_outbound_follows
table, the rows are stored in string order of the followed user's username. While this isn't
critical to the functionality of our application, it's a happy
bonus
feature of the data struc-
ture we've chosen.
Now let's explore the other side of the relationship, "Who follows
bob
?" To reinforce our
earlier discussion of query-driven design, let's first try to answer that question using the
user_outbound_follows
table:
SELECT "follower_username"
FROM "user_outbound_follows"
WHERE "followed_username" = 'bob';
When we attempt this query, we see an error that is familiar from the previous chapter:
Cassandra is unable to efficiently perform the lookup we're asking for, so we're required to