Lapis的数据库查询分页功能

作者：leafo

翻译：糖果

Pagination分页

Using the paginated method on models we can easily paginate through a query that might otherwise return many results. The arguments are the same as the select method but instead of the result it returns a specialPaginator object.

使用paginated方法，我们可以很轻松的实现多检索结果的分页效果。select方法的参数都是一样的，但是返回的查询结果是特定的Paginator对象。

For example, say we have the following table and model: (See Database Schemas for more information on creating tables.)

例如,下面的数据表定义：（Database Schemas那章有更多关于表创建的细节内容。）

create_table("users", {
  { "id", types.serial },
  { "name", types.varchar },
  { "group_id", types.foreign_key },

  "PRIMARY KEY(id)"
})

local Users = Model:extend("users")

We can create a paginator like so:

我们可以创建一个分页器：

local paginated = Users:paginated("where group_id = ? order by name asc", 123)

A paginator can be configured by passing a table as the last argument. The following options are supported: per_page: sets the number of items per page

可以在最后一个参数传个lua table定义来配置分页器，下面的这个设置：per_page:设置每页显示项目的条数。

local paginated2 = Users:paginated("where group_id = ?", 4, { per_page = 100 })

prepare_results: a function that is passed the results of get_page and get_all for processing before they are returned. This is useful for bundling preloading information into the paginator. The prepare function takes 1 argument, the results, and it must return the results after they have been processed:

prepare_results:此函数在把结果传给get_page和get_all处理之前就返回了。这个为分页器提前绑定信息很有用。预处理函数有一个参数，结果， 在返回结果之后就已对处理完了。

local preloaded = Posts:paginated("where category = ?", "cats", {
  per_page = 10,
  prepare_results = function(posts)
    Users:include_in(posts, "user_id")
    return posts
  end
})

Any additional options sent to paginated are passed directly to the underlying select method call when a page is loaded. For example you can provide a fields option in order to limit the fields returned by a page.

在页面被加载后，任何附加参数被传给分页器都直接被select底层方法调用， 例如你可提供一个fields 选项，来限制每页返回的字段数。

Whenever possible you should specify an ORDER clause in your paginated query, as the database might returned unexpected results for each page. The paginator has the following methods:

可能你在查询中指定了一个ORDER语句， 数据库的分页数据可能返回了非预期的结果，分页器有下面的这些方法。

get_all()

Gets all the items that the query can return, is the same as calling the select method directly. Returns an array table of model instances.

返回所有的查询项目，效果和直接调用select一样，Model返回的是lua的数组table。

local users = paginated:get_all()
SELECT * from "users" where group_id = 123 order by name asc
get_page(page_num)

Gets page_numth page, where pages are 1 indexed. The number of items per page is controlled by theper_page option, and defaults to 10. Returns an array table of model instances.

取得提定的page_num页， 页数从1索引 ，每页多少项是通过per_page这个参数来控制的，默认是10， Model返回的是lua的数组table。

local page1 = paginated:get_page(1)
local page6 = paginated:get_page(6)
SELECT * from "users" where group_id = 123 order by name asc limit 10 offset 0
SELECT * from "users" where group_id = 123 order by name asc limit 10 offset 50

num_pages()

Returns the total number of pages.

返回总页数。

total_items()

Gets the total number of items that can be returned. The paginator will parse the query and remove all clauses except for the WHERE when issuing a COUNT.

返回总项目数，分页器会分析查询，当查询是COUNT就移去WHERE之句。

local users = paginated:total_items()
SELECT COUNT(*) as c from "users" where group_id = 123

Returns an iterator function that can be used to iterate through each page of the results. Useful for processing a large query without having the entire result set loaded in memory at once.

返回一个迭代函数，被用于遍历每页的结果。在有大型查询处理，内存一次放不下时，很有用。

for page_results, page_num in paginated:each_page() do
  print(page_results, page_num)
end

Be careful modifying rows when iterating over each page, as your modifications might change the pagination order and you may process rows multiple times or none at all.

遍历每页中行要小心，你的编辑可能会改变分页的顺序，还有你可能会对行结果处理多次，或一次也没有。

has_items()

Checks to see if the paginator returns at least 1 item. Returns a boolean. This is more efficient than counting the items and checking for a number greater than 0 because the query generated by this function doesn’t do any counting.

检查处理，判断分页器至少返回一条数据。返回的是boolean值，这个比判断返回结果>0更高效，因为用这函数生成不会产生任何的计数查询。

if pager:has_items() then
  -- ...
end
SELECT 1 FROM "users" where group_id = 123 limit 1