官术网_书友最值得收藏!

Paginating through results

In most situations, we will want to avoid displaying an arbitrary number of results to a user in a single response. Instead, we will display a fixed number of results, and give the user an interface to load additional pages of the same size. In an SQL database, pages are typically specified using the OFFSET keyword, but CQL does not have this capability. Instead, we'll use the natural ordering of primary keys to put a lower bound on the key values displayed on a given page.

Let's insert a couple more rows into our table to get a sense of how pagination works using the following INSERT statements:

    INSERT INTO "users"
("username", "email", "encrypted_password")
VALUES (
'carol',
'carol@gmail.com',
0xed3d8299b191b59b7008759a104c10af3db6e63a
);

INSERT INTO "users"
("username", "email", "encrypted_password")
VALUES (
'dave',
'dave@gmail.com',
0x6d1d90d92bbab0012270536f286d243729690a5b
);

Now that we've got four users in our system, we can paginate over them. To save ourselves the trouble of adding numerous additional entries by hand, we'll just use a page size of two to demonstrate the process. We'll start by retrieving the first page as follows:

    SELECT * FROM users
LIMIT 2;

The LIMIT part of the query simply tells Cassandra to return no more than two results:

Now that we have our first page, we want to get the second. It would be nice if we could simply ask for the next primary key in order after dave using the following SELECT statement:

    SELECT * FROM "users"
WHERE "username" > 'dave'
LIMIT 2;

Unfortunately, this will give an error:

The message is a bit cryptic, and we don't know what exactly a partition key is yet, but it does contain a helpful hint: we can use the token() function to do what we want. The reason that our attempted query doesn't work is that, as we noticed before, the primary keys in our users table are not stored in lexical order; Cassandra can only return rows in the order in which they are stored.

The actual ordering is determined by the token of the primary key—the way the token is calculated is opaque to us, but Cassandra lets us use the token() function to retrieve the token for a given value:

    SELECT "username", token("username")
FROM "users";

Now we can see why the rows are returned in the order they are; they ascend by token:

Armed with this function, we can retrieve the next page of results as follows:

    SELECT * FROM "users"
WHERE token("username") > token('dave')
LIMIT 2;

And just as we'd hoped, the next two rows are returned as shown next:

Using this technique, we can paginate over arbitrarily large tables using multiple queries.

It bears emphasizing that retrieving large result sets from tables structured like users is a relatively expensive operation for Cassandra. In Chapter 3, Organizing Related Data, we'll begin to develop a more advanced table structure that will allow us to retrieve batches of rows very efficiently.

主站蜘蛛池模板: 仪征市| 武宁县| 桐庐县| 德格县| 平凉市| 陇南市| 内江市| 黄冈市| 福建省| 绍兴市| 大同县| 泰来县| 东城区| 巴中市| 永康市| 康乐县| 崇阳县| 科技| 岑巩县| 公主岭市| 莎车县| 镇沅| 珠海市| 漳平市| 崇州市| 西和县| 平陆县| 田阳县| 澜沧| 莲花县| 湘阴县| 康马县| 兰州市| 廊坊市| 祁门县| 静安区| 崇左市| 札达县| 宜良县| 贵港市| 万宁市|