ページ処理しながら全件数を一撃で得るSELECT文

前提

ブログリストみたいな記事データがある。 リストのページで表示するためには記事データの件数と１ページのデータを取得する必要があります

経緯

お恥ずかしいのですが前提のようなデータがあった場合に mysql だと一発でとれない思い込んでいました

今までは記事の件数を取得する SQL と 1 ページを取得する SQL を別々に発行していました。 アクセスがなかったり、データがそこまでないサイトなら問題ないのですがデータ数も多くてアクセス数も多い場合に SQL を 2 発なげるのはよくない状況になりました

mysql の機能でなんとかできないのかなと思ってググってみた所 with を使えばいけるということがわかりました

zenn.dev

このサイトを参考に記事を書いてます

実際の SQL

今までの SQL

１発目で件数取得

 SELECT  `id`, `categoryId`, `insDate`,`luDate`  FROM blog_data
    WHERE FIND_IN_SET( ? , categoryId) > 0

２発目でページ処理したデータを取得する

 SELECT  `id`, `categoryId`, `insDate`,`luDate`  FROM blog_data
    WHERE FIND_IN_SET( ? , categoryId) > 0
    ORDER BY insDate
    LIMIT ? OFFSET ?

一発で取得する SQL

WITH cte AS
    ( SELECT  `id`, `categoryId`, `insDate`,`luDate`  FROM blog_data
    WHERE FIND_IN_SET( ? , categoryId) > 0
    ORDER BY insDate )
        SELECT *, (SELECT COUNT(*) FROM cte) AS total_count FROM cte
    LIMIT ? OFFSET ?;

?の部分はバインド部分で mysql のクラスが変換してくれます

結果

id	categoryId	insDate	luDate	total_count
1	category1	2024/01/01	2024/01/01	100
2	category1,category2	2024/01/02	2024/01/03	100
3	category2	2024/01/03	2024/01/03	100
4	category3	2024/01/04	2024/01/05	100
5	category2,category3	2024/01/05	2024/01/05	100

total_count の部分が記事件数です

デメリット

指定するオフセット値が総レコード数 (total_count) 以降となる範囲外を指定すると、総レコード数が不明になります。返すレコード数が 0 になるためです。

対策として、範囲外となった時の挙動をアプリケーション仕様として事前じ決めておく必要があります。単に見つからないとするか、1 ページ目に誘導する等の UI/UX が絡む部分になります。

参考サイトより引用

0 件になるのはちょっといけてないけどパラメータを変えない限りはおきないので そこまで気にしていないです