Database Reference
In-Depth Information
Ordering and pagination
If our service is even slightly successful, a user's timeline is going to contain thousands or
tens of thousands of status updates. We will, of course, want to show only the few most re-
cent by default, and allow the user to paginate through older ones. Fortunately, Cassandra
allows us to use the
ORDER BY
clause in a query that specifies multiple partitions using
the
IN
keyword; under the hood, Cassandra will perform an ordered merge of the rows
from the specified partitions.
Let's assume that we only want to show two status updates per page. Accordingly, we'll add
the
LIMIT
and
ORDER BY
clauses to our query:
SELECT "username", "id", UNIXTIMESTAMPOF("id"), "body"
FROM "user_status_updates"
WHERE "username" IN ('carol', 'dave')
ORDER BY "id" DESC
LIMIT 2;
Note that, as always, the column given to
ORDER BY
is the first clustering column; this re-
mains the only valid argument to
ORDER BY
.
Just as we hoped, we now receive the results in descending order of creation time, regard-
less of partition key. This is a powerful feature of Cassandra, as it allows us to break out of
the narrow constraints of single-partition range queries:
If we want to retrieve the second page, we can use the same strategy that we used to pagin-
ate over slices in a single partition: simply look for the next-newest ID values following the
ones we've already seen:
SELECT "username", "id", UNIXTIMESTAMPOF("id"), "body"
FROM "user_status_updates"
WHERE "username" IN ('carol', 'dave')
AND "id" < 3a59c320-28cf-11e4-8069-5f98e903bf02
