NPocoの紹介 -機能編- ページング

最初の記事はこちら

今回はページング機能について紹介します。
(「全115件中11～20件目を表示」とかの機能を実現するためのものです。)

公式ドキュメントだとこちらに記載されています。

データベースはPostgreSQLを使用しました。

テーブルの用意

emp

前回と同じものを流用

自動クエリの場合

生SQLを使わない自動生成クエリの場合、終端操作でToPage メソッドを呼ぶことでページングを実現できます。

Page<Emp> page = database.Query<Emp>()
                        .Where(x => x.DeptCode == "001")
                        .OrderBy(x => x.EmpId)
                        .ToPage(2, 10);

以下のクエリが実行されます。

SELECT COUNT(*) FROM "emp" "E"
WHERE ("E"."dept_code" = @p0)
         -> @p0 [String] = "001"


SELECT "E"."emp_id" as "EmpId", "E"."first_name" as "FirstName", "E"."family_name" as "FamilyName", "E"."dept_code" as "DeptCode", "E"."created_at" as "CreatedAt", "E"."updated_at" as "UpdatedAt"
FROM "emp" "E"
WHERE ("E"."dept_code" = @p0)
ORDER BY "EmpId" ASC
LIMIT @p1 OFFSET @p2
         -> @p0 [String] = "001"
         -> @p1 [Int64] = "10"
         -> @p2 [Int64] = "10"

全件数取得のクエリと、現在のページ部分のデータを取得するクエリの2つが実行されます。

ToPage の第１引数はページ番号(1スタート, 0じゃないよ)
第２引数は1ページあたりの件数です。

上記の引数だと「1ページ当たり10件で2ページ目を取得」なので
LIMIT 10 OFFSET 10 というクエリが生成されています。

またOrderBy で常に同じ並び順になるように注意してください。

`Page<>` について

ソースはこちら

以下のプロパティが定義されています。

CurrentPage 現在のページ(メソッドの第１引数と同じ)
TotalPages 総ページ数
TotalItems 総件数
ItemsPerPage １ページ当たりの件数(メソッドの第２引数と同じ)
Items 現在のページのレコード

Limit メソッドについて

Limit メソッドを使っても似たようなことが可能です。

List<Emp> list = database.Query<Emp>()
                        .Where(x => x.DeptCode == "001")
                        .OrderBy(x => x.EmpId)
                        .Limit(10, 10)
                        .ToList();

以下のクエリが実行されます。

SELECT "E"."emp_id" as "EmpId", "E"."first_name" as "FirstName", "E"."family_name" as "FamilyName", "E"."dept_code" as "DeptCode", "E"."created_at" as "CreatedAt", "E"."updated_at" as "UpdatedAt"
FROM "emp" "E"
WHERE ("E"."dept_code" = @p0)
ORDER BY "EmpId" ASC
LIMIT @p1 OFFSET @p2
         -> @p0 [String] = "001"
         -> @p1 [Int64] = "10"
         -> @p2 [Int64] = "10"

ただしこちらは、第１引数はスキップするレコード数となっており、戻り値もList<> となっています。

生SQLを記述する場合

生SQLを使う場合Page メソッドを呼ぶことでページングを実現できます。

string sql = @"
SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @deptCode
ORDER BY
  emp_id
";
var param = new
{
    deptCode = "001"
};

Page<Emp> page = database.Page<Emp>(2, 10, sql, param);

以下のクエリが実行されます。

SELECT COUNT(*) FROM (SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @p0
) npoco_tbl
         -> @p0 [String] = "001"

SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @p0
ORDER BY
  emp_id

LIMIT @p1 OFFSET @p2
         -> @p0 [String] = "001"
         -> @p1 [Int64] = "10"
         -> @p2 [Int64] = "10"

クエリ自動生成の場合と同じく全件数取得のクエリと、現在のページ部分のデータを取得するクエリの2つが実行されます。

また同様に Page の第１引数はページ番号、第２引数は1ページあたりの件数です。

このページングについては

Database.BuildPageQueries
PagingHelper
DatabaseType.BuildPageQuery (データベース別のオーバーライドあり)

によって実現されています。

クエリには必ずORDER BYの記述を忘れないようにしてください。

Fetch メソッドについて

実は Fetchメソッドもオーバーライドされており、ページングのクエリを発行できます。
ただしこちらの戻り値は List<> であり、件数取得は行われません。

string sql = @"
SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @deptCode
ORDER BY
  emp_id
";
var param = new
{
    deptCode = "001"
};

List<Emp> list = database.Fetch<Emp>(2, 10, sql, param);

以下のクエリが実行されます。

SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @p0
ORDER BY
  emp_id

LIMIT @p1 OFFSET @p2
         -> @p0 [String] = "001"
         -> @p1 [Int64] = "10"
         -> @p2 [Int64] = "10"

Page と同じく第１引数はページ番号です。

SkipTake メソッドについて

SkipTake メソッドでもFetchメソッドと似たようなことは可能です。
ただしこちらの第１引数は「スキップする件数」です。

string sql = @"
SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @deptCode
ORDER BY
  emp_id
";
var param = new
{
    deptCode = "001"
};

List<Emp> list = database.SkipTake<Emp>(10, 10, sql, param);

以下のクエリが実行されます。

SELECT
  emp_id
FROM
  emp
WHERE
  dept_code = @p0
ORDER BY
  emp_id

LIMIT @p1 OFFSET @p2
         -> @p0 [String] = "001"
         -> @p1 [Int64] = "10"
         -> @p2 [Int64] = "10"

注意点

非常に便利なページングの機能ですが注意点があります。

生SQLを使う場合 with 句を使うことができません。
(クエリ加工を正規表現で行っており、対応していない)

なのでwith を使わないクエリを書くか、with を使う場合は自前のページングをする、ビューを作成するなどの対応が必要となります。