Web Api でずれないページネーションとSQL

　何とは言いませんが、登録された写真を日付順に閲覧できるアプリがあるとしましょう!
　それには登録された写真を日付順に取得できるWebAPIが必要ですね〜。
数十枚とかであるならば一括で取得すればいいですが、１０万件など大量にデータがあるときは話が別ですね。
　その場合は、10件ずつなど順番に取得させますよね。パラメータにoffsetとlimitなどをオフセットと取得数を指定してオフセットさせながらページネーションさせせます。そしてUIてきには、スクロールの度にApiを叩いて読み込む仕様にしますね。
　この時offsetに0とか10とか単純に先頭からのオフセット分を指定する仕様にするとすると、例えばページネーション中に新しい写真が登録されたり削除され場合に、ページネーションがズレることが考えられますね。考えついてしまいますね。とても気持ち悪いです。

Offsetでのページネーション（BAD）

簡易パターンで、自分的バッドパターン。
offsetに先頭からのオフセット、amountに取得数という仕様。順番は日付順です。
offsetに50でlimitに10を指定すると日付順で５１番目から６０番目までを取得する

API仕様(offset)

GET https://hoge/v1/photographs.json?offset=0&limit=10

APIレスポンス仕様(offset)

{
    "photographs": [
        {
            "id": 967,
            "date": "2016-06-03T12:10:00",
            "url": "https://hoge/gysu.jpg"
        },
        {
            "id": 34,
            "date": "2016-06-03T12:09:00",
            "url": "https://hoge/fds.jpg"
        }
    ]
}

サーバー内部ではsqlでoffset句を利用

SQL(offset)

SELECT 
    `p`.`id` 
FROM 
    `photo` AS `p`
ORDER BY 
    `p`.`date` DESC
LIMIT 
    10 
OFFSET 
    0

date新着順で順番に取得してますが、offsetで100を指定している間に写真が１枚追加された場合に、次のoffset110を指定して取得すると、追加前の100番目から109番目を取得してしまいます。本当は101番目から110番目までをとりたい。

Stream形式でのページネーション

日付で順次絞り込み

　FileInputStreamみたいに今の場所から相対的に連続的に読み込むのでストリーム形式と仮に呼んでおきます。
今回の場合は日付新着順なので、since_dateに日付を指定してamountに取得数という仕様。
　since_dateより古いデータを取得します。
　次のデータをリクエストする場合はsince_dateに前回取得した最後のデータのdateを指定します。そうすることで今の位置から相対的に絞り込んでデータを取得します。途中でデータが追加されても日付でページネーションしてるのでズレません。
例を挙げると、TwitterのAPIでは、since_idというのを指定するとそれ以降のツイートを取得します。ツイートはidが登録順に順番に割り当てられるのでidで以降か以前かを絞り込むと自然と新着順もしくは古い順で連続的に読み込めます。
since_dateでやるならばこんな感じ。

API仕様(stream)

GET https://hoge/v1/photographs.json?since_date=2016-06-03T12:09:00&limit=10

APIレスポンス仕様(stream)

{
    "photographs": [
        {
            "id": 256,
            "date": "2016-06-03T12:08:00",
            "url": "https://hoge/bvg.jpg"
        },
        {
            "id": 654,
            "date": "2016-06-03T12:07:00",
            "url": "https://hoge/ews.jpg"
        }
    ]
}

サーバー内部ではsqlでwhereで絞り込んでいく

SQL(stream)

SELECT 
    `p`.`id` 
FROM 
    `photo` AS `p`
WHERE 
    `p`.`date` < ?
ORDER BY 
    `p`.`date` DESC 
LIMIT 10

順番が確定的ではない場合（同じ日付のデータが現れる可能性のある場合）

　上記の例では日付順で取得していますが同じ日付のデータが有った場合に順番が確定的ではないです。ページネーションのちょうど端っこに現れた場合、同じ日付のデータがスキップ（もしくはdate <= '2016-06-04 00:49:31'にした場合は同じデータが重複）されてしまう。
　そこで重複しないプライマリーキーであるidを使用します。dateとidの２つで順番を定義させます。
　日付が被った場合に、idで順番付けをします。
※ここの例ではidは登録順になっていない例とするのでidだけのソートでは日付順にならない例としてます。

SQL(stream-date-id)

SELECT 
    `p`.`id` 
FROM 
    `photo` AS `p`
WHERE 
    `p`.`date` <= ?
AND 
    NOT (`p`.`date` = ? AND `p`.`id` >= ?)
ORDER BY 
    `p`.`date` DESC, `p`.`id` DESC 
LIMIT 10

やっていることは行値式の代替です。行値式が使えるのであればそちらで問題なし。
mysqlでは行値式はindexを使用できないとかdbによっては行値式サポートしてないとか便利な構文だけどやっかいごとがあるので、見難いですが下記ではなく、上記をよく使ってます。
一応参考までに、行値式で書くと

行値式

 WHERE (`p`.`date`, `p`.`id`) < (?, ?)

Apiとして、「何順」など意識させず、since_dateやsince_idなどの概念を持ち出したくない場合

page_tokenとしてsince_dateとidを隠蔽します。
先ほどの例でページネーションに必要なデータはdateとidです。
サーバー側で取得した最後のデータのidとdateを、レスポンスのnext_page_tokenに、jsonなりにしてbase64なりでエンコードして一緒に返します。
　クライアントは、このnext_page_tokenを次のリクエストのpage_tokenパラメータに渡します。Api側では渡されたらデコードしてdateとidに分解して利用。

API仕様(page_token)

GET https://hoge/v1/photographs.json?page_token=fdTf65Kh9GLKO&limit=10

APIレスポンス仕様(page_token)

{
    "next_page_token" : "Ki6D45eE1Jhyt09K",
    "photographs": [
        {
            "id": 256,
            "date": "2016-06-03T12:08:00",
            "url": "https://hoge/bvg.jpg"
        },
        {
            "id": 654,
            "date": "2016-06-03T12:07:00",
            "url": "https://hoge/ews.jpg"
        }
    ]
}

こうしておけば、いろんなパターンのApiが合っても、サーバー側の都合を隠しつつ、クライアントからは同じページネーションの仕様で共通化できて使いやすいですね。

まとめ

　という感じで妥協なきページネーションを実現して気持よくリリース！

人気順などの、時間が経つと順番が変わるパターンもストリーム形式では解決しきれない部分があるのでまた今度書いてみる。(´°̥̥̥̥̥̥̥̥ω°̥̥̥̥̥̥̥̥｀)